July 10, 2013 at 4:17 am
We are planning to change sql 2005 server to windows authentication mode.
everything is working fine except linked server.
when set the permission as "Be made using the login's current security context". when i run a job with a domain user (as alias) , which will used by application to connect database i get the below error.
Executed as user: Domain\username. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274). The step failed.
any ideas.
Regards
Durai Nagarajan
July 10, 2013 at 4:26 am
Deos the account that runs the SQL agent have access to the DB on the linked server.
July 10, 2013 at 4:42 am
we have two accounts one that is running sql agent and the one i am trying
when i execute it using the first one looks fine
the second one i have updated it as run as user in job , this is the one which is failing.
i want have this executed as application will try using this id.
Regards
Durai Nagarajan
July 10, 2013 at 6:19 am
Try granting the SQL AGENT account read access to the remote db in question. The job will be running with those credentials.
July 10, 2013 at 6:39 am
not working
my id is sysadmin on both servers, i get same error
but the service id which is mapped on sql services is working am i missing something.
Regards
Durai Nagarajan
July 10, 2013 at 6:46 am
What about the other acoount used for the APP. I imgine they are both domain accounts.
Do you use Kerberos in your environment.
What happens if you use the 'Be made using this security context' Under security and explicitly use the credentials and then click the test connection button.
July 10, 2013 at 6:56 am
Bobby Glover (7/10/2013)
What about the other acoount used for the APP. I imgine they are both domain accounts.
yes
Do you use Kerberos in your environment.
NO
What happens if you use the 'Be made using this security context' Under security and explicitly use the credentials and then click the test connection button.
do you want use it with the domain accounts password?
Regards
Durai Nagarajan
July 10, 2013 at 6:59 am
if i run a query on SSMS using linked server with my windows login.
i got this error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"
Regards
Durai Nagarajan
July 10, 2013 at 7:03 am
And you are sysadmin on both servers?
Looks like Keberos issue. Cannot hop you credentials between servers.
All sorts of things to look into.
Server Principal Name's etc. Accounts need to have delegation setup in AD.
You need to sort this out with your windows admin\domain admin.
You need to google Kerberos and SQL linked server. Or something to that effect.
July 10, 2013 at 7:07 am
Other than Kerberos no other way for windows authentication?
Regards
Durai Nagarajan
July 10, 2013 at 7:46 am
Run this
SELECT net_transport, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
See what Authentication method you are using.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply