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.
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
