Hi,

I've spent 5 hours on a clients server trying to get this linked server to work MSSQL Express to MSSQL standard both 2005.

ServerA (SQL Express 2005)
ServerB (SQL Standard 2005) (running on port 9050) (can connect and authenticate fine from SQL Management Studio)

First of all I create a link by right clicking "Linked Servers" on ServerA and clicking to create a new linked server. I then select the "Server type" as being "SQL Server". I Enter the server name and port in this case: ServerB,9050

When I click OK I get the following error:

- - - - - - - - - - - - - - -
"The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?"

Additional information
An exception occurred while establishing a Transact-SQL statement or batch.
(Microsoft.sqlserver.Express.ConnectionInfo)

The OLE DB provider "SQLNCLI" for linked server "ServerB,9050" reported an error Authentication failed.

Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "ServerB, 9050". (Microsoft SQL Server, Error: 7399)
- - - - - - - - - - - - - - -

So this is obviously expected as the other server (serverB) is also on another domain so windows authentication wont work.

So i go about changing the security. I put a check in "Be made using this security context", Enter my remote SQL user and password. Click OK and i receive the following error:

- - - - - - - - - - - - - - -
"The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?"

Additional information
An exception occurred while establishing a Transact-SQL statement or batch.
(Microsoft.sqlserver.Express.ConnectionInfo)
TCP Provider: A socket operation encounted a dead network
(Microsoft SQL Server, Error: 10050)
- - - - - - - - - - - - - - -

Now i dont get how it's suddenly giving me a message like the server doesnt exist when specifying a username and passowrd?

Regards
Liam Wheldon

Is the account you are using to connect to the linked server even existing on the linked server?

if you are using john_doe as your login, it must exist on the linked server isn't it?

Hi,

Yes the login exists as i can login to ServerB,9050 with the username and pooassword that i'm specifying in the linked server credentials.

It still wouldn't explain getting network dead compared to the first login failiure when not specifying credentials.

Regards

Liam