March 14, 2012 at 4:35 am
Hi,
I have a SQL Server 2008R2 version which is configured to run with a local machine account. Lets call it A.This server has a linked server to another SQL Server ,say B.
Now, i have an agent job on A which executes some code on Server B remotely. This job fails with an error below:
Executed as user: A\UNITY. Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [SQLSTATE 28000] (Error 18452). The step failed.
Looks like because 'A' is running with a local system account, therefore B is not able to recongnize it. Is there a way to solve this problem. Please help.
Regards
Chandan
March 14, 2012 at 4:38 am
Sure you can solve it. Do not use the local system account 🙂
Or execute the job through a proxy.
Creating SQL Server Agent Proxies
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 14, 2012 at 4:46 am
Koen Verbeeck (3/14/2012)
Sure you can solve it. Do not use the local system account 🙂Or execute the job through a proxy.
🙂
I cannot change the local system account:-(
I am considering the option to run the job as proxy. Let's say we use proxy by creating one and then use it in 'run as user' option in the job step. What connection will go to remote server B. Will it be proxy connection?
currently, in the event viewer of B, I am seeing:
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed.
March 14, 2012 at 4:51 am
The connection will be the same. The credentials using that connection will however be that of the proxy account.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 14, 2012 at 4:57 am
Thanks for your link above. To create a proxy, I needed to create a credential but while creating a credential, I got an error:
an error occured during decryption Error: 15466 :w00t:
Is there an easier way to create a proxy?
Regards
Chandan
March 14, 2012 at 5:00 am
You have two options:
create a proxy through TSQL, or use the Management Studio Interface.
I think the last one will be the easiest.
At which step did you get that error?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 14, 2012 at 5:04 am
Koen Verbeeck (3/14/2012)
You have two options:create a proxy through TSQL, or use the Management Studio Interface.
I think the last one will be the easiest.
At which step did you get that error?
I read from the link that for creating a proxy, I need to have a credential. So while creating a credential,I put its name, the account and passwords. Then got the error when I tried to click on 'OK'.
March 14, 2012 at 5:17 am
Also, in the microsoft link, it is mentioned that:
Job steps that execute Transact-SQL do not use SQL Server Agent proxies. Transact-SQL job steps run in the security context of the owner of the job
My job step has direct T-SQL code and not some procedures so proxy might not work for me. I put the owner as 'sa' but still the connection that goes to remote server is the local system account 'A\Unity'.
March 14, 2012 at 5:53 am
Ah ok. Maybe change the owner of the job to another account than sa?
sa is a built-in account, so that also uses the local system account for connecting.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 14, 2012 at 6:26 am
Koen Verbeeck (3/14/2012)
Ah ok. Maybe change the owner of the job to another account than sa?sa is a built-in account, so that also uses the local system account for connecting.
I changed the job owner to a windows account which is sysadmin on A. Following error is recorded in the job:
Could not obtain information about Windows NT group/user 'Doamin\account', error code 0x5. [SQLSTATE 42000] (Error 15404)).
March 14, 2012 at 6:31 am
Are server A and B in the same domain?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 14, 2012 at 6:50 am
Koen Verbeeck (3/14/2012)
Are server A and B in the same domain?
Both the servers are in the same domain. One is running under a local account and other is running under domain account.
I can login to both machines using my domain account. I cannot even create a linked server, it gives me authentication fail.
March 14, 2012 at 7:09 am
what account is the SQL Agent running as on A? Is it local system? Could you add a login to B which is DOMAIN\A$ and give it the access it needs? Which should allow local system to connect.
On the linked server front, could you not create the linked server using a SQL authentication account (if your in mixed mode).
March 14, 2012 at 7:33 am
anthony.green (3/14/2012)
what account is the SQL Agent running as on A? Is it local system? Could you add a login to B which is DOMAIN\A$ and give it the access it needs? Which should allow local system to connect.On the linked server front, could you not create the linked server using a SQL authentication account (if your in mixed mode).
A is having local accounts for both sql services and agent services.
For linked server, it gives me an error: anonymous logon failed
March 14, 2012 at 7:38 am
is that authentication error using windows accounts or sql accounts?
can you post screen shots of the linked server config?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply