How do I connect to a Linked Server?

  • The following string has been placed in my global.asax file:

    Application("dbdsn")="data source=localhost;integrated security=sspi;database=TESTSERVER.TestDB"

    The following Server Error occurs:

    Server Error in '/' Application.

    Cannot open database "TESTSERVER.TestDB" requested by the login. The login failed.

    Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

    (?) Do I need to setup a domain account? Or are my permissions possibly not set correctly?

  • You need to have a domain account if the database needs to talk with other database servers.

    I saw people use NT Authority account to start SQL Server, but I never see people use this account to link a database. May I be correct.

  • Is your database really on a linked server connected to your local machine? I did not know that you could do that. Why are you not just directly connecting to the other server through .NET?

    How are the permissions setup for the linked server?

  • I've created a domain account called MYDOMAIN\SQLTEST1

    (?) What areas in Sql Server 2005 do I need to grant this domain account permission to?

    I am assuming permission rights must be granted on both servers.

    (?) Do I need to make any changes to my IIS 6.0 setup?

  • Jack:

    In Sql Server 2005, I ran the below Query:

    EXEC sp_addlinkedserver

    'TESTSERVER',

    N'SQL Server'

    GO

    Replace TESTSERVER with your server you want to link.

  • Now you need to create a login on your server for your domain account and on the linked server with rights to whatever you are trying to access. Then , on the local server, you need to run sp_addlinkedsrvlogin (this link will give you the syntax http://technet.microsoft.com/en-us/library/ms189811.aspx), to map the local user to the linked server.

    I really don't think your connection is going to work anyway. I think what you want is to connect directly to TESTSERVER in your ASP.NET application. For the linked server setup to work you would need to set your ASP.NET connection to be to the local server, then in your queries you query the linked server using select x from linked_server.database.schema.table syntax.

  • Jack:

    My connection string is already using 'localhost'

    Receiving this error:

    Server Error in '/' Application.

    Cannot open database "TESTSERVER.TestDB" requested by the login. The login failed.

    Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

    Could there be a problem with the SID being different between servers?

    Would I just use EXEC sp_addlinkedsrvlogin 'TESTSERVER' on my local?

  • I think the problem is that the application is trying to connect to a db named testserver.testdb on the local server and that db does not exist on that server. As I said before I think your app should be connecting directly to the correct server. I have never tried to directly connect to a db on a linked server from an app.

  • Jack:

    What would be the correct syntax using 'localhost'?

    I've tried the following connection strings:

    Application("dbdsn")="data source=localhost;integrated security=true;Database=TestDB"

    Error:

    Server Error in '/' Application.

    Cannot open database "TestDB" requested by the login. The login failed.

    Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

    Application("dbdsn")="data source=localhost;integrated security=sspi;database=TESTSERVER.TestDB"

    Error:

    Server Error in '/' Application.

    Cannot open database "TESTSERVER.TestDB" requested by the login. The login failed.

    Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

    Application("dbdsn")="data source=localhost;integrated security=sspi;Server=MECHINTRANET;database=TestDB"

    Error:

    Server Error in '/' Application.

    Login failed for user 'MY_DOMAIN\ALEX$'.

    **ALEX is my computer name.

  • I think you are misunderstanding how a linked server works. A linked server is designed to allow you to query another data source from within the context of an existing SQL Server connection. So if you are in SSMS you query a linked server using openquery, openrowset, or using 4-part naming. My favorite way is the 4-part naming convention like this:

    Select * from linked_servername.database.schema.table

    So you have 2 options:

    1. Connect to localhost as you are currently except set the database to master. Then when you right your query use the 4-part naming I mention above like: select * from testserver.testdb.schema.table This should work. I have tested it here.

    2. Your connection string should be: data source=testserver;integrated security=sspi;database=TestDB

    Then your query would be select * from schema.table The only issue here is getting integrated security to work in ASP.NET. You should search for that elsewhere. It can be done, but is a hassle.

    Option 2 would be the option you really should use. Linked Servers are sloooow.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply