I have the following query in a stored procedure that runs on SERVER1 to the instance of a linked server, .. [SERVER \ SQLSVR]
SELECT ArticulosID, DepositosID, Cantidad
FROM [SERVIDOR\SQLSVR].DB.dbo.Stocks
A C# application uses the procedure with integrated security. Users do not have access to [SERVER \ SQLSVR] it is a remote server, but yes, obviously to SERVER1. Users log in to SERVER1. How can I do so that users can request data through that stored procedure ?, I have tried but I get an unknown or anonymous login error .... Thanks a Lot!!
This sounds to me like the double hop issue, whilst you are passing your credentials from the users computer to the 1 SQL Server the SQL Server then uses an anonymous connection to the second one. To get around this you need to set up Kerberos authentication on the 1st SQL Server if you want to pass the users credentials through seamlessly.
The alternative method is to create a dedicated login on the second server with the required permissions and then set up the linked server to use that login. As you can imagine this does open up a security hole in that any person connecting to the 1st server can get access to the linked server databases.
It's not too difficult, you just set up the SQL Server Service account with delegated rights (for Kerberos) in active directory then create your SPN's.
Once you have done that run this query on your 1st server and the result should come back as Kerberos and not NTLM.
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
Just created a mapping linking windows user with a sql user login at Servidor,
EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u';