Sql-server – Linked Server Error – Login Failed for user ‘theaccount’

linked-serversql server

I have a database on serverA. I have a windows account (myAccount) that is a member of db_datareader role on this db.

On serverB, I want to create a linked server to the database on server using myAccount.

However, I get Login Failed for user 'myAccount'.

Are there any other permissions I need to set?

Best Answer

In linked servers - you'll need to make sure you have permissions on both servers that you're looking to set the link between.

So for instance - if you're sa on Server A, you'll still need at least read-only access to the database you're trying to access on Server B in order to use the linked server.

Additionally - when you're configuring your linked server, it's important to make sure your security context is set correctly.

By default, Linked Servers are made without any security context which puts the responsibility for authentication on the connection string you specify.

Usually you'll want to use the credentials of who is attempting to connect through the linked server by selecting "Connections will be made using the login's security context", but this varies based on you and your companies needs.

More information on linked servers can be found on technet:

General Linked Servers - https://msdn.microsoft.com/en-us/library/ms188279(v=sql.105).aspx

Linked Server Security - https://technet.microsoft.com/en-us/library/ms188477(v=sql.105).aspx

Related Solutions

Sql-server – getting a “login failed” when creating this linked server

Run below TSQL :

EXEC master.dbo.sp_addlinkedserver 
                            @server = N'serverB', 
                            @srvproduct=N'', 
                            @provider=N'SQLNCLI', 
                            @datasrc=N'serverB', 
                            @catalog=N'MyDatabase',
                            @provstr='Integrated Security=SSPI;'
GO
EXEC sp_addlinkedsrvlogin 'serverB', true  
go

Check if the "serverB" exists in sys.servers :
```
select * from sys.servers
```

Check if the "serverB" works from ServerA

select * from serverB.master.dbo.sysdatabases

Sql-server – How to get the linked server working using Windows authentication

Every machine in the chain from your desktop to the server you are calling has to be Kerberos enabled for the trust to advance past the first hop. So, yes the server needs to trust the user for delegation.

The "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" almost always indicates a delegation problem.

Your Windows Account must have access to both ServerA and ServerB.
You must not have the setting "Account is sensitive and cannot be delegated."
Both ServerA and ServerB must have their own SPN registered.
The servers must be TCP/IP or named pipes connected.

The SQL Server Books Online article that offers some more detail is "Configuring Linked Servers for Delegation": http://msdn.microsoft.com/en-us/library/ms189580(v=sql.105).aspx

Best Answer

Related Solutions

Sql-server – getting a “login failed” when creating this linked server

Sql-server – How to get the linked server working using Windows authentication

Related Question