If you take the normal approach to create a linked server to SQL Azure Database, then you would end up in below error message.
Here is the text of the error message
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
Reference to database and/or server name in ‘sqlauthority.sys.sp_tables_rowset2’ is not supported in this version of SQL Server. (Microsoft SQL Server, Error: 40515)
SOLUTION
For me, here is the configuration in Azure
Server name – sqlauthority.database.windows.net
Database name – sqlauthority
Here are the steps I have taken to create linked server. I went to new linked server and on “General”, I used below:
- Linked Server (name): LinkedServerName
- Provider: Microsoft OLE DB Provider for SQL Server
- Product name: (blank)
- Data Source: azure_db_server.database.windows.net
- Provider string: (blank)
- Location: (blank)
- Catalog: database name in Azure
Then on “Security” tab, I used below.
- Be made using this security context
- Remote login: azure-database-user-name
- With password: password
Once done, hit OK.
Here is the information via linked server
This is same what we saw when we were directly connected. I hope this blog can help you in fixing linked server errors to SQL Azure Database. Let me know if you have ever faced the same situation before. I would love to know your feedback in the comments.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
7 Comments. Leave new
What If we have multiple Databases? In catalog section can we specify multiple Database names??
I too am trying to configure a Linked Server from on-prem SQL Server and Azure with an Azure SQL Server that hosts multiple databases. I am using a SQL Login/User with SQL Authentication that has permissions in all of my Azure databases, and the same SQL login and password exist on the on-prem server. I am using the same data source and configuration you have above. I can successfully view and query one database, but no matter how I configure this I cannot both view and query multiple databases. Is it possible to create a linked server that references multiple catalogs?
In Azure database , i dont see options for Server Objects, where we can create Linked Server, so far my understanding is The Azure SQL Database (Software as a service) does not have linked servers and the alternate is Cross-Database Queries, Please help
https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/
In my case, Azure DB is destination i.e. linked server is created on On-Prem. I guess you are trying reverse.
Is there a way to create this LinkedServerConnection from a traditional SQL Server to AzureSQLDatabase without using SQL Authenticated account? I am looking to leverage traditional ActiveDirectory account (or even a AzureActiveDirectory account).
Once again, your advice was FLAWLESS!
can you create a linked server from an azure managed instance to an azure sql database\//