Learnings from the Trenches - Unable to query sys table via Linked Server

Published Dec 28 2021 05:42 PM 2,618 Views
Microsoft

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.

 

Diego_N_0-1640741837160.png

 

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.

 

Diego_N_1-1640741837163.png

 

Test your connection as a save measure…

 

Diego_N_2-1640741837165.png

 

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:

 

Diego_N_3-1640741837168.png

 

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 order to successfully query system tables. Shown in the sample below.

 

Diego_N_4-1640741837169.png

 

Click Security… and follow the same steps above:

 

Diego_N_5-1640741837171.png

 

Test connection… can you spot the difference in server name?

 

Diego_N_6-1640741837173.png

 

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

 

Diego_N_7-1640741837175.png

 

 

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

Co-Authors
Version history
Last update:
‎Dec 28 2021 05:52 PM
Updated by:
www.000webhost.com