January 13, 2009 at 1:13 pm
I have 64-bit Windows 2003 Server with a SQL Server 2005 on it. I am trying to use a linked server on this 64-bit server to a database on a SQL Server 2005 32-bit server. I have created the linked server, but I can not see the tables. I get an error:
OLE DB Provider "SQLNCLI" for linked server "test" returned message "login timeout expired"
OLE DB Provider "SQLNCLI" for linked server "test" returned message "An error has occured while establishing a connection to the server. when connecting to sql server 2005, this failure may be caused by the fact that uder the default setting SQL Server does not allow remote connections."
I have allowed remote connections for the database using the Surface Area Configuration tool, and I can connect to the database via an ODBC datasource.
I am not sure if its login or security issue. Can you explain the type of local logins I need? If I have an local login, do I need a remote user and remote password? These are the Lilnked Server security properties. Do I need add permission on the test database?
Thanks very much.
January 14, 2009 at 7:42 am
You almost certainly have a security issue. To link servers in SQL Server 2005, you must use a SQL Server ID, not a Windows Authenticated ID. It makes no difference whether one is 64-bit and the other 32-bit (unless the 64-bit is an Itanium, in which case, all bets are off).
January 14, 2009 at 8:47 am
Irish Flyer (1/14/2009)
You almost certainly have a security issue. To link servers in SQL Server 2005, you must use a SQL Server ID, not a Windows Authenticated ID. It makes no difference whether one is 64-bit and the other 32-bit (unless the 64-bit is an Itanium, in which case, all bets are off).
I believe you can pass the Windows ID if Kerberos is enabled and proper SPN's are setup. That being said, when I have used linked servers in the past I have created a specific SQL Server login on the linked server (test, in your case) for use only by linked servers. Then this login is granted the necessary permissions within the database(s). Then in the linked server you can either use the local server to remote server login mappings to restrict use of the linked server or you can use the For a login not defined in the list above, connections will: And specify Be made using this security context (your linked server user).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 20, 2009 at 2:28 pm
I created a login on the TEST SQL Server and placed it into the database and gave it the proper rights to read the DB. Then, I changed the password on the ODBC data source (Linked Server). I used "With SQL Server authenicationj using a login ID and password entered by the user." So, I entered the same user name and password. Got the following error:
Connection Failed. SQLState 42000 SQL Server Error 18488. Login failed for user LinkedServer. Reason: Password of the account must be changed.
I also used the same account on the LinkServer security properties tab and got this error:
Named Pipes Provider: Could not open a connection to SQL Server [53]
OLE DB Provider "SQLNCLI" for linked server "TEST" return message "Login timeout expired"
OLE DB Provider "SQLNCLI" for linked server "TEST" return message "An eror has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the defualt settings SQL Server does not allow Remote connections.
Do you have any tips on what could have gone wrong?
January 20, 2009 at 2:40 pm
With SQL Server 2005 and above there are password policies available fro SQL logins and one of the options is that the user must change password on next login and it looks like that is wha tyou have for an option. Use SSMS to login to the server using that account and change the password.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 20, 2009 at 2:53 pm
Thanks, that fixed the problem with the account and the data source.
I still have the error message with connecting with the Linked Server. On the TEST server, I do have remote connections enabled.
January 20, 2009 at 4:22 pm
The ID for connection must have CONNECT privileges as well as read. Can you connect directly to this ID on the TEST server through Management Studio. If not, that is probably the problem. Also, when setting up the link, define the link using the desired ID, but also make the ID the default connection. Sometimes that will work.
January 20, 2009 at 4:34 pm
Yes, with this Windows ID, I can connect using Management Studio and see the databases on the other server. Also, the UDL Universal Data Link can connect to the other SQL DB. This is on the Linked Server. Also, I am linking from a 64-bit server to 32-bit. Both are SQL 2005 servers/dbs. Are there any issues with this? What settings can I look at?
Thanks.
January 21, 2009 at 6:55 am
It makes no difference whether the systems are 32 or 64-bit. They will all talk to each other just fine. I am puzzled, though. You said you could connect using "this Windows ID". The ID should not be a Windows ID, but a SQL Server ID. There should not be a corresponding Windows ID with the same name on that server. Also make sure that security for the instance allows both Windows Authentication and SQL Server Authentication.
January 21, 2009 at 2:07 pm
Sorry, for the confusion. I created a login account on the SQL Server where I am trying to connect to and added the login to the database. The account seems to work with ODBC datasource and UDL, but not via Linked Server. The instance of SQL Server does have Windows authenication.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply