SQL Server to SQL Server Linked Server

Copper Contributor

I have two sql servers.  a SQL 2019 and SQL 2022.

 

SQL 2019 - Instance name STILL

SQL 2022 - Instance name PRICE

 

Both servers are set up with Windows Auth Only - no SQL server Auth.

 

I am trying to create a linked server on the SQL 2022 PRICE Instance to the SQL 2019 STILL Instance and am not having any luck.

 

I have added a "server account" named DOMAIN\svc_SQL19_RO to both server and gave it sysadmin just to test.  Once I get this working I would want it to just be a Read Only account.

 

Since these are instances I have them using non standard SQL ports and I have set up the SPNs using...

 

Output here

 

C:\Users\myuser>setspn -L DOMAIN\svc_SQL19_RO
Registered ServicePrincipalNames for CN=svc_sql19_RO,OU=Service Accounts,DC=DOMAIN,DC=LOCAL:
MSSQLSvc/SQL19.DOMAIN.LOCAL:1450
MSSQLSvc/SQL22.DOMAIN.LOCAL:1460
MSSQLSvc/SQL19:1450
MSSQLSvc/SQL22:1460

 

I have gone into AD and under the service account delegation tab i have added the SPNs above to the service account.

 

As for linked server setup - standard set up.  Clicked the SQL Datasource and added using 

"Be made using this security context"

 

Thank you in advance.

John

 

No firewall issues - we use these instances on a daily basis etc.

4 Replies

not having any luck.


@Dolejh76 , "no luck" means what in detail?

Hello,
What is the error message that you are getting ?

Regards
Javier
@olafhelper / @Javier Villegas

EXEC master.dbo.sp_testlinkedserver N'SQL19_LINKED'

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'DOMAIN\svc_SQL19_RO'.

---

I am able to connect to both servers using SSMS locally with 'DOMAIN\svc_SQL19_RO'. Again I have that account set up as a sysadmin just for testing, but when i use the account with the linked server it does not work.

Additional information - appears that when I added the SPNs for this account - i broke remote SSMS access for all users. I have SPNs set up for the managed service accounts that I use for the SQL services. I think it cause an issue due to the port being used on two SPNs. Once I removed the SPNs from svc_SQL19_RO - I was able to login to SQL19 again with remote SSMS.

Thanks for your help looking at this - I have a ton of other linked servers set up but those are all using OLEDB, or ODBC, or SQL Auth. This specific one is a SQL to SQL with Windows Auth and everything I have tried is not working. You would think this would be the easiest to set up!

I was able to get this to work using the option "Be made using the login's current security context" which i took as meaning the "logged in user" and it worked - however the permissions are somehow elevated. I tested with a logged in user to SQL19 that had sysadmin on SQL19 but only RO access on SQL22 and they were able to create, modify and drop tables on the SQL22 database.

At the end of the day - I have a DB admin that should have full access to SQL22 database, and via a linked server had Read Only access to the database that resides on the SQL19 server. (using windows auth)