December 15, 2008 at 4:56 am
Hi,
I have just installed SQL Server 2005 Standard edition as a named instance on a brand new Server running Windows Server 2008. Everything appears to have installed correctly and I have applied the service packs requested by the installation.
However, when I attempt to add a linked server I receive the following error message
"The test connection to the linked server failed"
An exception occurred while executing a Transact-SQL statement or batch
The OLE DB provider "MSDASQL" for linked server "HTON" reported an error. Authentication failed
Cannot initialise the data source object of OLE DB provider "MSDASQL" for linked server HTON etc.
The last part of the error message
OLE DB provider "MSDASQL" for linked server "HTON" returned message
"[Informix][Informix ODBC Driver][Informix] User(s)'s password is not correct for the database server.".(Microsoft SQL server, Error: 7399)
The Informix ODBC driver is correctly installed and configured. I have set up a system DSN and that works fine. I have also tried OPENROWSET and get a similar error. I have tried to add a linked server to an access database, also without any luck.
The local system account has been used for the installation
There is an instance of SQL Server 2008 desktop running on the box but that was installed during OS installation and I have no idea what it is for.
Any ideas anyone? I am at my wits end.
Quis custodiet ipsos custodes.
December 15, 2008 at 6:29 am
Hi,
I have solved the problem. The error is generated when I add a linked server but fail to add my Management Studio user name as a local log in. When I added my user, the error vanished and I was able to use OPENQUERY and extract data from the linked server.
It appears that in Management Studio version 9.00.3042.00 creating a linked server and clicking on OK causes a connection test to be made. If you have not added your Management Studio user name to the local users list, the test will fail reporting the above error. Earlier versions of Management Studio do not appear to perform this test(I tried it myself on an earlier version) and do not offer a test connection option when a link is right clicked.
I was just about to pull out the rest of what is left of my hair.
regards
Shaun
Quis custodiet ipsos custodes.
January 12, 2010 at 9:53 pm
Thanks for the update on the status. It helps.
-gol
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply