0

From server1, I've created a connection to a remote server (server2) with the Linked Server facilities. Both servers are on the same domain. When I log to server1 I can successfully execute my stored procedures from SQL Server Management Studio but when I execute my stored procedures from my browser application I get the following error on server 2.

Error: 18456, Severity: 14, State: 58.
Login failed for user 'sa'. An attempt to login using SQL authentication failed.
Server is configured for Windows authentication only.

I've searched the Internet and Stackoverflow for solutions but I'm not sure what's the best way to solve this issue. I'm sure it involves adding/changing database security settings but I'm unsure where to make these changes.

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1405736
  • 365
  • 2
  • 4
  • 17
  • I think you're asking how to configure [passthrough authentication with a linked server](http://stackoverflow.com/questions/340431/sql-server-linked-servers)? The [documentation](http://msdn.microsoft.com/en-us/library/ms175537(v=sql.105).aspx) also explains that delegation needs to be enabled, although actually doing that can be tricky and has more to do with Windows than SQL Server. If you can possibly map local logins to remote SQL logins you'll find it much easier. – Pondlife Jun 04 '13 at 20:57

1 Answers1

0

Right-click the server in Enterprise Manager, select properties, goto Security, and change to SQL Server and Windows Authentication mode.

Screenshot

Mixed mode is required if you want to use sa or any other sql-account for authentication. However i believe that the recommended way is to keep the server at Windows authentication mode, and use domain accounts for authentication. The sa-account is the quick and dirty way.

If you have the exact same security setup on both servers, the link between them should work if you select "Be made using the login's current security context" in the linked server properties. That way, the account will be "inherited" in the connection to the linked server.

Screenshot

Check under Security - Logins on each of the server so that the same account is added to both servers. Also check the properties of both entries, under Server Roles you can try to add the sysadmin-role to make sure the accounts will have access to all databases on the server. You should however limit this access in live environments :)

Screenshot

TheQ
  • 6,858
  • 4
  • 35
  • 55
  • I don't need to use the sa account. To use a domain account, what do I need to setup on server1 and server2? – user1405736 Jun 04 '13 at 20:01
  • Updated the answer, by selecting "Be made using the login's current security context", the same domain account will be used when connecting through the link. – TheQ Jun 04 '13 at 20:06
  • I answered another question about linked servers a few days ago, maybe it will help you as well: http://stackoverflow.com/questions/16835134/mssql-connect-to-another-instance/16866714#16866714 – TheQ Jun 04 '13 at 20:10
  • Apparently the security settings are not the same. The radio button is already set to "Be made using the login's current security context" and I'm receiving the error above. With that said, what security changes need to be made on both servers to get this to work? – user1405736 Jun 04 '13 at 20:42
  • Make sure the domain account you are using are added under Security - Logins on both servers. I'm not sure what roles are required on the account, but if you have access to both systems, and they are not live, try granting the sysadmin-role on both logins. – TheQ Jun 04 '13 at 20:52
  • @TheQ Granting sysadmin permissions won't help, because the issue is that the SQL Server service account needs to be trusted for delegation, as [explained](http://msdn.microsoft.com/en-us/library/ms175537(v=sql.105).aspx) in the documentation. – Pondlife Jun 04 '13 at 21:02
  • So this is where I trail off on what should be done where. Do I add the domain account to the remote server login mapping on server1? Then on the remote server (server2), do I add the domain account to the SQL server properties, or the database, or the table, or the schema? Also, do I add the domain account as a New User on the database? – user1405736 Jun 04 '13 at 21:04
  • Added some more info, and a screenshot :) – TheQ Jun 04 '13 at 21:18
  • Still receiving the same error message: "Error: 18456, Severity: 14, State: 58. Login failed for user 'sa'. An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.". I used the same Domain account on both servers and not 'sa'. – user1405736 Jun 04 '13 at 21:31