December 12, 2009 at 1:09 am
Hi,
Recently, i got an issue with linked server.
Problem Description :
There is a job which calls a dts package in which calls stored procedure.
Actually, it has to get completed within 4 minutes and when i see sp_who2 this job is never being compeleted. It has taken more than 2 hrs still not completed.
so, i killed the job and again tried to execute the job but the result the same.
Then we opened the code inside the stored procedure and tried to execute each step inside the stored procedure then i got the below error.
SQL Server 2000: "Login failed for user "(null)". Reason: Not associated with a trusted SQL Server connection".
This error is never written to any log!!!! and so the job showing still running.....
Is there an way to find out/trace such Linked server problems??????????????????
Basically, we have a domain in which say for example CSD is my domain and i have 3 systems in my domain.
Machine - 1 acting as Active Directory in which i have 3 users as well belong to Admin group csd\u1,csd\u2,csd\u3.
SQL01 -- on which sql server 2000 is installed and Mixed mode authentication is turned on
SQL02 -- on which sql server 2000 is installed and Mixed mode authentication is turned on
Both are defualt instances!!
Now requirement is , there is a table "T1" in the second sql sever i.e SQL02. so there is a linked server on SQL01 to SQL02 to access t1 table
i.e select * from SQL02..t1
But it returns me an error saying
SQL Server 2000: "Login failed for user "(null)". Reason: Not associated with a trusted SQL Server connection".
How to trouble shoot this problem????????????
Observations from my side
--------------------------
1. on both the servers Mixed mode authentication is turned on.
2. i checked the Security in the Security tab in the Linked server properties in the Enterprise Mnaager.
There the radio button
-- Be made using the login's current security context is selected.
What does it mean?
-- is that Os login credentials are being passed or if i open the query analyser and login as sql authentication/Window Auth and execute the
select * from SQL01..T1 query
then current login credentials are passed???? which credentials passed?? i need clarity..
Meaning, even if i login to query analyzer using sql authentication, does the current sql login credentials are passed to the other instance SQL02 or no matter you login as Windows / SQL authentication , only OS login credentials are passed. Which one is correct ???????????
And how does the authentication is validated on the other server????? Does any impersanation takes place here ???
Can anybody give a big picture on this,what happens in the background?????
Next, for testing purpose , i used the "sa" for Remote login, then it is working fine and if i change back to the radio btn i.e.
* Be made using the login's current security context is selected.
It is again throwing me an error!!! Don't know what is happening.
We have other linked servers pointing to other server with the same security context. What would be the problem?
Is the problem with SQL02 instance it self?
Do we need to consult any Windows admin for this problem?
I have also attached a "security.JPG" file which shows the sample Environemt. Have a look at it.
Any solutions will be greatly appreciated.
Thanks in advance.
December 13, 2009 at 5:10 pm
Try this:
In SQL SERVER assign admin rights to your Domain Accounts (csd\u1,csd\u2,csd\u3)
December 14, 2009 at 3:46 am
if possible set up linked server again
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 15, 2009 at 7:21 am
You could also create a dedicated linked server SQL login on both servers with specific read/write permissions in a specified database if you did not want to grant sysadmin rights to the domain accounts. The sysadmin role would allow the domain accounts to do anything and everything within SQL Server including dropping tables, databases, etc... This could help close some security and auditing loopholes.
December 16, 2009 at 12:24 am
Please check the authentication mode under security. For all the linked servers, it should be Mixed mode. if the below error is logged in windows application log,
SSPI handshake failed with error code 0x80090304 while establishing a connection with integrated security; the connection has been closed.
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
then check whether all the servers are listening to the correct port and do add the servers in C:\WINNT\system32\drivers\etc\hosts file.
Thanks
December 16, 2009 at 2:02 am
login's current security mode means the following:
If on sql authentication:
Then the same user id and password are passed to the remote server.
with the same id and pwd one can login to the remote server then your query will run.Else nope.
So when you tried with SA its likely that the SA pwd shd have been different. so it failed.
So create a test acct with same pwd and try on both the servers.
If on windows authentication:
Same explanation holds good. difference it should be the same windows
account or the domain account. LocalAdministrator account wont work.
Should be domain account.And the domain account should be present in the SQL Server in the remote machine.
First test with these two. If it still fails then post here. I will confuse you further 🙂 ( just kidding. Windows authentication has few more things releated to it. If SQL authentication is ok with you then use that
as it is lot more simple )
Regards,
Raj
December 16, 2009 at 2:21 am
Nagaraj
as you have told that if we have different password for sa account , it will fail
In my one of my environment, we have same sa password on two diff servers.
so which option will be suitable for having different password for linked server setup.
coz we are planning to have diff password for seucurity prospect
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 16, 2009 at 2:46 am
Security wise Windows authentication is the best bet.If thats not an option then You can have the 'login under security context' and also
specify the login and password on the Local login and remote user/password boxes.
The login mapping happens as you have specified for all the logins listed while creating the linked server. For the ones not specified explicity 'login under security context' works.
Security wise, I dont endorse remotly mapping sa ( unless inevitable )
becos by hacking one sa account one can have sa access in other machine as well.
Regards,
Raj
December 16, 2009 at 3:00 am
thanks for it
please tell me we have another option like
1. be made without using security context
2. be made using login's current security context
what do we understand by these options ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 16, 2009 at 7:25 am
Bhuvnesh (12/16/2009)
thanks for itplease tell me we have another option like
1. be made without using security context
Does not use / require any kind of authentication, example if the source is an excel file or an access DB.
2. be made using login's current security context
Uses the Security context of the user (You) who is creating the Linked Server.
The answers are just short and simple answers, read Books Online for detailed and much more information.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 16, 2009 at 8:26 pm
If on sql authentication:
Then the same user id and password are passed to the remote server.
with the same id and pwd one can login to the remote server then your query will run.Else nope.
So when you tried with SA its likely that the SA pwd shd have been different. so it failed.
So create a test acct with same pwd and try on both the servers.
This is what i understood from the above one,
You mean to say, on both the instance's the "sa" passwd should be same!
if one instance through which am connectinto the other has one "sa" pwd and the target linked instance is having a different "sa" password then you say, the query will fail?
Am i right? correct me if am wrong?
Why is it so? Why should i keep same password for "sa" for all instances to which am connecting to linked servers?
December 16, 2009 at 8:36 pm
Please check the authentication mode under security. For all the linked servers, it should be Mixed mode. if the below error is logged in windows application log,
SSPI handshake failed with error code 0x80090304 while establishing a connection with integrated security; the connection has been closed.
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
then check whether all the servers are listening to the correct port and do add the servers in C:\WINNT\system32\drivers\etc\hosts file.
I checked the Security in the Security tab in the Linked server properties in the Enterprise Mnaager.
There the radio button
-- Be made using the login's current security context is selected.
December 16, 2009 at 8:47 pm
Your understanding is correct but If you read my post carefully sa password's shd be same only when you select the option
> To be made under user's security context.
What 'To be made under user's security context' implies is when I dont define a mapping explicitly for a sa account, then I will attempt to use my existing credentials ( user name/password ). so , if login thro sa then my account (sa) and sa pwd will be passed to the remote server. If I login thro acct1 then acct1 ID and pwd will be passed.
Regards,
Raj
December 16, 2009 at 8:50 pm
If your sa pwd's r different then map it explicitly as i mentioned earlier.
As I said earlier, in general, I dont like the idea of SA on linked servers
as it can be serious security breach.
Regards,
Raj
December 17, 2009 at 10:25 pm
If on windows authentication:
Same explanation holds good. difference it should be the same windows
account or the domain account. LocalAdministrator account wont work.
Should be domain account.And the domain account should be present in the SQL Server in the remote machine.
First test with these two. If it still fails then post here. I will confuse you further 🙂 ( just kidding. Windows authentication has few more things releated to it. If SQL authentication is ok with you then use that
as it is lot more simple )
Still the same error.
Am logging in as Windows Authentication.
csd\jason
And current security for the linked server is choosen as
-- Be made using the login's current secuity context
Also, in the Linked server properties -->Server options tab
Enabled i.e kept a check mark for the below items
Data Access
RPC
RPC Out
Use Remote Collation
Next step, i have done the following still not able to succeed
On the remote sql server which is in the same domain,
i have created a login csd\jason with windows auth and given db_owner for the corresponding databases which am accessing through linked server.
Still not working!!!
Step2
-------
Dropped the linked server and created it once again.
Still same error.
What should be my next step to trouble shoot this????
Any windows stuff needed to be changed??
But one thing, sql server authentication is working fine!!!!
But i want to figure out why am not able to do the same using windows authentication?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply