Forum Discussion
SQL Server to SQL Server Linked Server
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 ...
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!
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)
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)