Working with SQL Server Linked servers
Linked servers provide a great way to access data from various types of remote servers or database including but not limited to SQL Server, Windows Azure SQL Database, Active Directory, Oracle, Access, etc. Running distributed queries via Linked servers configured to use Windows authentication is a common scenario in many customer environments. This topic provides some useful resources that can help configure the linked server and also troubleshoot some of the common errors that are associated with this setup. Note: If you are running into an issue that is not covered in this curation, you can find quite a few KB articles or blogs both on Microsoft and elsewhere using your favorite search engine and using a search string that would be of the following format Sql “Linked server” “<error message”> For example: sql “linked server” “Login failed for user” To qualify this further you can use: Sql “Linked server” “<error message”> site: support.microsoft.com – This will give you any KB articles related to the topic. Sql “Linked server” “<error message”> site:blogs.msdn.com – This will provide links to various blogs posts on MSDN (replace msdn with Technet for Technet blogs) Sql “Linked server” “<error message”> site: msdn.microsoft.com – This will provide newsgroup postings on this topic.
22 Oct 2013
Basic concepts of Linked Servers
This Books Online topic helps understand the basic concepts of a linked server and provides additional links to topics that you can refer to for creating linked server using SQL Server Management Studio or using T-SQL.
Setting up a Linked Server for a Remote SQL Server Instance — DatabaseJournal.com
This article by Gregory A. Larsen on DatabaseJournal.com presents a very nice overview on setting up and basic usage of linked servers along with the screenshots for each step and what the options really mean. Though this seems to be created for SQL 2005, the same information still holds good even for the latest versions.
Linked Servers to SQL Azure
If you are one of the many customers who started using Windows Azure SQL Database for some of your workloads and want to harness the power of linked server queries for your data needs, you can check the following blog post for additional information on setting up the same.
Troubleshooting SQL Linked Server Query failed with “Login failed for user …” -
After the linked server has been setup, one of the major issues that our customers frequently run into is an error message that is similar to one of the following: “Login failed for user “(null)”. Reason: Not associated with SQL Server Connection “Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' These errors typically occur when you select “Be made using the login’s current security context” or check the “Impersonate” column in the Security page of the linked server configuration. Both these settings direct the SQL Server process to pass the windows credentials (domain credentials) of the user that submitted the distributed query (on Server 1 or the middle server) to the remote SQL Server. For these settings to work, you need to ensure that Kerberos (a security mechanism that will help ensure transfer of Windows user tokens between multiple servers in your environment) is already configured between the servers. This is also referred to as security account delegation. The following post from one of our product team members provide a great overview on the causes of these problems and the steps you need to take to resolve these issues in linked server configurations. Please note that you may need to work with your Active directory/Domain administrator to get all these setup.