October 17, 2022 at 1:52 pm
I'm facing an issue that has me stumped. Server A is linked to Server B via "be made using this security context" where the ID it uses is a SQL authenticated user on Server B which we'll refer to as LinkedUser. When I set up a data connection to Server A, and pull data from tables that reside in Server A, in either Excel or Access it completes successfully both using my Windows authenticated account and a separate SQL authenticated user which will be referred to as AppUser. If I write a query using that same connection but this time reference a table from the linked server and run it using my Windows authenticated account then it completes successfully. However, if run the same query using the AppUser SQL authenticated user then I get the error "Access to the remote server is denied because no login-mapping exists." So the error only happens when using a SQL authenticated user within another app and it does not allow it to link to the other server. Any ideas why this would be?
October 17, 2022 at 2:36 pm
Kerberos?
Or maybe the app simply isn't passing the credentials correctly?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2022 at 2:53 pm
I've dealt a little bit with the double hop issue but this seems to be a little different in the sense it only impacts SQL authenticated users.
The app is passing credentials correctly because it can query tables on the local server, just not on the remote using the linked server.
October 17, 2022 at 3:22 pm
To add more confusion, it will work if I make AppUser a sysadmin on Server A. However, this isn't an option to keep.
October 17, 2022 at 4:49 pm
There is a limitation here I think you are running into. You cannot have code that hops from outside one server and then hops to another via linked server. Your application must get the data from both servers and use it. It sounds like maybe you are querying the one server and then that query (which sits on the server), in turn, queries another liked server. This "double-hop" cannot be completed for security reasons. The application that is querying must query both servers, as opposed to querying one server with some code that calls to a second linked server. (This will fail outside of the first server no matter what type of account you use (microsoft or sql authenticated) because the second server cannot confirm the identity of the account used to query the first server.)
I am making a lot of assumptions here, so correct me if that is not what you are doing.
October 17, 2022 at 5:39 pm
That is exactly what is happening from a process flow standpoint. However, it succeeds with Windows authenticated accounts so that's why it isn't lining up in my mind.
October 18, 2022 at 9:26 pm
Need a little more info to get a clear picture. Does the query succeed perhaps when you are running the query from within server A but not succeed when you are running it from outside the server (eg: from Excel/Access)?
Or does the query actually succeed using appuser in Excel/Access when appuser is a windows domain account?
October 24, 2022 at 5:40 am
I think that when you log on with your SQL account you have a security context of that account. When you then do the linked server bit there is a conflict between the security context you are running under and that used by the linked server. When you use a SQL account to login, you are explicitly choosing to use a restricted security contex.
The reason it works when your SQL account is sysadmin is that you are running under the security context of the service account, not that of the SQL account.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 25, 2022 at 7:44 am
Suggest you try a different configuration in your Linked Server Properties: link both of your "Local Login" names to the same "Remote User", with the same "Remote Password" configured for both.
With this configuration, you want the radio-button for connections "not be made" for logins not defined in the list.
This is not the most secure way of doing things, but should work for your scenario.
Effectively, your login(s) on Server A will all share the same login on Server B; difficult to accurately audit on Server B (if audit is an issue, you might want to look at separate Linked Servers, or some other configuration).
As already mentioned, Kerberos is the preferred, and more secure way, of authenticating.
October 25, 2022 at 4:49 pm
I think that this will only work if the windows user you are using for the linked server is the same user as in your app and that user would then also have to be a SysAdmin on your server A in order to use the linked server to server B. From a security standpoint, therefore, it's not a good way to go... If your app ID gets compromised, they could do anything they want on your server A if the account you are using is a sysadmin account. Just my two cents. It's one of those things you might be able to get away with, but you might not wish you had, someday.
October 26, 2022 at 7:09 am
I tend to configure a SQL Login on Server B (rather than Windows Login). The Linked Server on Server A is configured with this SQL Login (simple username and password) to access Server B. It would be crazy to use a sysadmin account for any application; there is little additional risk here. There is no requirement to be a sysadmin on Server A to make use of the Linked Server; you might need to be sysadmin to *configure* the Linked Server, but that is different.
Yes, if the application is compromised, they get access to not only Server A but also Server B; but only via the SQL Login on Server B, which is suitably restricted.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply