In this article I will show how to resolve and overcome errors while attempting to query a system tables or any table for that matter, via Linked Servers. The error seen below is the result of the issue we encountered.
Msg 40515, Level 16, State 2, Line 1
Reference to database and/or server name in 'xxxx.sys.sp_tables_info_90_rowset_64' is not supported in this version of SQL Server.
According to research this error occurs due to the 'master' db doesn't allow direct connections in sqldb. In order to pass the error, the data source connection needs to be specified, we must direct the linked server to connect to user database; in this case mine being 'css'. In addition, security setting should reflect the user/pass used in Azure under the New linked Server wizard.
Let's duplicate this using the wizard.
It is commonly mistaken to use the full FQDN and the server type 'SQL Server' here in this first step. By simply assuming the 'Linked Server' name should be the Azure SQL Database to which is our target database. Don't click OK just yet, click on security first.
Under Security, choose 'Be made using the security context' option to connect. The user and password must be the same admin user used in the Azure server. Then click OK, this will create the newly created server.
Test your connection as a save measure…
Now, give it a whirl… open a new query window under the new linked server use the following statement to duplicate the error.
select * from [youservername.database.windows.net].dbname.sys.tables
Voila! You should see the below results:
While the connection is successful, querying system tables isn't allowed because the 'master' database is currently selected. Additionally, you are not able to use the 'USE' function as it isn't supported in Azure SQL Database; remember you're still attempting to query an SQLDB thus rules apply here as well.
So, how do we fix this? Welp! We have to create a new Linked Server; you cannot modify connection setting on an existing Linked Server. Here's a good article written by a colleague, please follow this link to see How to create Linked server from SQL Server to Azure SQL Database
This time around we will be using the data source selection that will allow you to specify a database - or a user database - in other to successfully query system tables. Shown in the sample below.
Click Security… and follow the same steps above:
Test connection… can you spot the difference in server name?
Then change the syntax just a bit, use the new server name given to your connection - in this case - AZURECSSLS_TEST (but behind scenes is actually querying my server/database in Azure) this is because of the small change done to the data source.
select * from [azurecssls_test].dbname.sys.tables
And... That's it! You have successfully overcome this pesty error. In hope this helps you, 'til next time in another learning from the Trenches!
Enjoy!
Diego Nieto
Sr. Technical Advisor
Azure SQL Database - CSS team
Posted at https://sl.advdat.com/3EEGPTE