Hello people,
Good night!
In this post I will talk about an error that occurs when trying to use the OPENROWSET or OPENQUERY command to access data from a remote server where the linked server used does not have a fixed user and the connection user does not belong to role sysadmin, which represents the majority. of the cases. (plus one error reported by Henrique Mauri.. kkk)
Msg 7416, Level 16, State 2, Line 22
Access to the remote server is denied because no login-mapping exists.
This error is very similar to the “double hop” scenario that occurs in Kerberos, where a client application is on computer 1, BizTalk RFID is on computer 2, and the resource that requires credentials (such as an SQL server Server RFIDsink) is on computer 3, but in this post I will show a solution using SQL Server only. If you want to go deeper in solving the problem using Kerberos, take a look in this post.
Simulating the error in your environment
A practical way to simulate this problem is to create a new linked server with the parameter @ useself = N'True ', informing that the same user will be used at both ends of the connection, which will be the user currently connected, and then try to use this linked server.
As already mentioned above, this error message only appears when the user running the query NO be part of role sysadmin and the connection user is using SQL Server authentication (instead of Windows Authentication).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
---------------------------------- -- CRIAÇÃO DO LINKED SERVER ---------------------------------- USE [master] GO DECLARE @instancia NVARCHAR(200) = N'127.0.0.1\SQL2014' IF ((SELECT COUNT(*) FROM sys.servers WHERE name = @instancia) > 0) EXEC master.dbo.sp_dropserver @server=@instancia, @droplogins='droplogins' EXEC master.dbo.sp_addlinkedserver @server = @instancia, @srvproduct=N'SQL Server' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@instancia,@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL ---------------------------------- -- CRIAÇÃO DA TABELA DE TESTES ---------------------------------- SELECT * FROM OPENROWSET('SQLNCLI', 'Server=127.0.0.1;Trusted_Connection=yes;', 'SELECT * FROM Testes.dbo.Teste') AS a |
How to solve the problem
From what I mentioned above, it is clear that if the connecting user is added to role sysadmin, this problem will stop occurring, but I don't even consider it a solution. Granting privileges (even more than sysadmin) just to stop error messages is never a solution.
I am also not very much in favor of setting the username and password on the Linked Server, so that all users with access to the server can use this Linked Server in an untraceable way. Always check the option “Be made using the login's current security context” on LinkedServer.
A good solution would be to use Windows Authentication, but as the development team of the company where this problem occurred would not even consider changing the authentication of all applications to Windows Authentication, the best solution I found for this problem was to create a new user only for OPENROWSET and OPENQUERY uses, with restricted access only for queries made with these two commands (which are very few) and set this user and password in OPENROWSET, thus:
|
1 2 |
SELECT * FROM OPENROWSET('SQLNCLI', 'server=127.0.0.1\SQL2014;Uid=Usuario_Teste;Pwd=aaa', 'SELECT * FROM Testes.dbo.Teste') AS a |
And that's it folks!
If you know of a different solution to this problem, leave your suggestion in the comments.
Hug and even more!
