March 12, 2008 at 3:53 pm
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?
March 12, 2008 at 7:54 pm
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.
March 12, 2008 at 7:55 pm
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?
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
March 13, 2008 at 6:03 pm
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?
March 13, 2008 at 6:05 pm
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.
March 13, 2008 at 7:06 pm
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 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
March 14, 2008 at 12:14 pm
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?
March 14, 2008 at 12:26 pm
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 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
March 14, 2008 at 12:49 pm
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.
March 14, 2008 at 1:17 pm
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.
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
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy