0

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?

Improve this question
7
  • 1
    How are you configuring the security? If you are using passthrough, then you need to make sure that the servers can do AD impersonation. But if that is configured correctly, then you need to make sure you have the "myAccount" with permissions into ServerB.
    – Jonathan Fite
    Dec 30, 2015 at 15:49
  • I agree with @JonathanFite. It looks like that user may not be set up on Server B. If you want to narrow it down, check the SQL Logs on Server B, if you have Login Auditing set to 'Failed logins only', you should see a failed attempt along with the exact reason, whether that be bad password, no such user, etc. Aaron Bertrand has a great list of what the 'State' messages translate to here: sqlblog.org/2020/07/28/troubleshooting-error-18456
    – Kris Gruttemeyer
    Dec 30, 2015 at 15:53
  • 1
    As @JonathanFite said - you need to ensure Kerberos authentication is working correctly and the SQL Server Service Account used by server "b" is trusted for impersonation. This question may help.
    – Hannah Vernon ♦
    Dec 30, 2015 at 15:56
  • 1
    On your linked server - on the security tab - make sure you're using "Connections will be made using the login's current security context" otherwise you're likely not passing anything. You COULD also specify a service account to be used for your linked server, but that all depends on your security needs. If you apply a service account, anyone who has access to that DB can also use that linked server.
    – 8bit
    Dec 30, 2015 at 16:09
  • 2
    Sounds like you entered an account manually and specified the password under Be made using this security context: That is for SQL Authentication only and is not how Windows Authentication works. The documentation states: The Remote User must be a SQL Server Authentication login on the remote server.
    – Aaron Bertrand
    Dec 30, 2015 at 16:22

1 Answer 1

Reset to default
3

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

Improve this answer
4
  • So for Windows Authentication I use... "When connecting to the local server using Windows Authentication (recommended), select Be made using the login's current security context to connect to the remote server using the same Windows Authentication credentials". How then do I know what account it is using? It needs to be a specific windows service account.
    – K09
    Dec 31, 2015 at 11:03
  • If it needs to be a certain service account, just below "using the current users credentials" there's "be made using this account" that allows you to specify a service account. You just enter the credentials for the account (if its an AD account, be sure to list the domain [i.e. domain\account]) and the linked server will use that account for all connections. This goes back to that account needing access on Server B, though.
    – 8bit
    Dec 31, 2015 at 14:25
  • This only works for a sql user. Not an AD account
    – K09
    Dec 31, 2015 at 15:28
  • You're right, that was an oversight on my part. If you're locked in to using a windows account, you'll need to take the suggestions made by Jonathan Fite & Max Vernon above. Be sure you have impersonation enabled and your Kerberos settings are in check. Alternatively, assuming you're running a remote stored procedure, you could always use EXECUTE AS to impersonate a windows account inside the stored procedure.
    – 8bit
    Dec 31, 2015 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.