Solved

Receiving errors when trying to query to a linked SQL server

Posted on 2007-07-31
7
337 Views
Last Modified: 2012-05-05
I need to create a stored procedure for reporting on SQL server #1 that references a SELECT from SQL Server #2.

I linked the #2 server to server #1 but if I try to do a simple select

SELECT * from [HQ-SQL2K].Ceridian.dbo.employess I get the following error

OLE DB provider "SQLNCLI" for linked server "HQ-SQL2K" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.


Server #1 is Windows 2003 SP1 with SQL 2005 SP1, Server #2 is Windows 2003 SP1 with SQL 2000 SP4
0
Comment
Question by:PROJHOPE
  • 4
  • 3
7 Comments
 
LVL 14

Expert Comment

by:twoboats
ID: 19602861
" Not associated with a trusted SQL Server connection."

The login you are using on server #1 isn't set up as a trusted login on server #2

You need to map the login across to the linked server - see below

sp_addlinkedsrvlogin
Creates or updates a mapping between logins on the local instance of Microsoft® SQL Server" and remote logins on the linked server.

Syntax
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
    [ , [ @useself = ] 'useself' ]
    [ , [ @locallogin = ] 'locallogin' ]
    [ , [ @rmtuser = ] 'rmtuser' ]
    [ , [ @rmtpassword = ] 'rmtpassword' ]

Arguments
[@rmtsrvname =] 'rmtsrvname'

Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.

[@useself =] 'useself'

Determines the name of the login used to connect to the remote server. useself is varchar(8), with a default of TRUE. A value of true specifies that SQL Server authenticated logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. false specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server. true for useself is invalid for a Windows NT authenticated login unless the Microsoft Windows NT® environment supports security account delegation and the provider supports Windows Authentication (in which case creating a mapping with a value of true is no longer required but still valid).

[@locallogin =] 'locallogin'

Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows NT user. The Windows NT user must have been granted access to SQL Server either directly, or through its membership in a Windows NT group granted access.

[@rmtuser =] 'rmtuser'

Is the username used to connect to rmtsrvname when useself is false. rmtuser is sysname, with a default of NULL.

[@rmtpassword =] 'rmtpassword'

Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.

Return Code Values
0 (success) or 1 (failure)

Remarks
When a user logs on to the local server and executes a distributed query that accesses a table on the linked server, the local server must log on to the linked server on behalf of the user to access that table. Use sp_addlinkedsrvlogin to specify the login credentials that the local server uses to log on to the linked server.

A default mapping between all logins on the local server and remote logins on the linked server is automatically created by executing sp_addlinkedserver. The default mapping states that SQL Server uses the local login's user credentials when connecting to the linked server on behalf of the login (equivalent to executing sp_addlinkedsrvlogin with @useself set to true for the linked server). Use sp_addlinkedsrvlogin only to change the default mapping or to add new mappings for specific local logins. To delete the default mapping or any other mapping, use sp_droplinkedsrvlogin.

Rather than having to use sp_addlinkedsrvlogin to create a predetermined login mapping, SQL Server can automatically use the Windows NT security credentials (Windows NT username and password) of a user issuing the query to connect to a linked server when all these conditions exist:

A user is connected to SQL Server using Windows Authentication Mode.


Security account delegation is available on the client and sending server.


The provider supports Windows Authentication Mode (for example, SQL Server running on Windows NT).
After the authentication has been performed by the linked server using the mappings defined by executing sp_addlinkedsrvlogin on the local SQL Server, the permissions on individual objects in the remote database are determined by the linked server, not the local server.

sp_addlinkedsrvlogin cannot be executed from within a user-defined transaction.

Permissions
Only members of the sysadmin and securityadmin fixed server roles can execute sp_addlinkedsrvlogin.

Examples
A. Connect all local logins to the linked server using their own user credentials
This example creates a mapping to ensure that all logins to the local server connect through to the linked server Accounts using their own user credentials.

EXEC sp_addlinkedsrvlogin 'Accounts'

Or

EXEC sp_addlinkedsrvlogin 'Accounts', 'true'

B. Connect all local logins to the linked server using a specified user and password
This example creates a mapping to ensure that all logins to the local server connect through to the linked server Accounts using the same login SQLUser and password Password.

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', NULL, 'SQLUser', 'Password'

C. Connect all local logins to the linked server without using any user credentials
This example creates a mapping to ensure that all logins to the local server connect through to the linked server mydb without using a login or password (mydb does not require a login or password).

EXEC sp_addlinkedsrvlogin 'mydb', 'false', NULL, NULL, NULL

-or-

EXEC sp_addlinkedsrvlogin 'mydb', 'false'

D. Connects a specific login to the linked server using different user credentials
This example creates a mapping to ensure that only the Windows NT user  Domain\Mary connects through to the linked server Accounts using the login MaryP and password NewPassword.

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'NewPassword'

E. Connects a specific login to an Excel spreadsheet (the linked server)
This example first creates a linked server named ExcelSource, defined as the Microsoft Excel spreadsheet DistExcl.xls, and then creates a mapping to allow the SQL Server login sa to connect through to ExcelSource using the Excel login Admin and no password.

EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\MyData\DistExcl.xls',
   NULL,
   'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL

0
 

Author Comment

by:PROJHOPE
ID: 19603063
Thanks what I don't understand is the following:

Both servers are mixed authentication...so I could use a local SQL account or a Windows network login.

I was going with the Windows network logon because this logon existed on both servers.  Yet I guess I don't grasp the concept.

If I enter int he user ID that equals on both servers is there a need to put in the impersonate and the remote user ID and login?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19603123
Is the windows account mapped to the same SQL login on both servers?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:PROJHOPE
ID: 19603428
The windows account is not mapped to a local SQL login for both servers.  It is simpley DOMAIN/userID

0
 
LVL 14

Expert Comment

by:twoboats
ID: 19603499
Is it added in to SQL though? You have to add that DOMAIN/userID as a sql login, or at least add a windows group that DOMAIN/userID belongs to as a sql group.

0
 

Author Comment

by:PROJHOPE
ID: 19603530
Yep...it is shown under SQL Security as DOMAIN/userID its sql login is the same DOMAIN/userID

Let's take this a step back...

If I have one account that exists on both servers then on Server #1

I would select from the drop down the Windows authentication user ID
Do I choose the impersonate?
Do I enter in the remote login and password?

This verbage is about as clear as mud (just like the Database Mail setup)
0
 
LVL 14

Accepted Solution

by:
twoboats earned 50 total points
ID: 19603799
If exactly the same DOMAIN/userID exists on both servers, then I believe you want to run

exec sp_addlinkedsrvlogin @rmtsrvname = 'server#2', @useself = 'useself'

on server #1
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now