SQL Server does not exist or access denied.

[mori0043]

What am I doing wrong? I am trying to connect to a web server that connects to another machine running SQL server 2K. I receive this error: SQL Server does not exist or access denied.

Here is my conn string: Provider=sqloledb;Data Source=asdas;Network Library=DBMSSOCN;Initial Catalog=Cadets;User ID=sa;Password=sa;

I have all ready checked the settings on SQL Server and mixed mode is checked....

Any ideas?????

 

[jbenson001]

Check the configuration of your connection string:

http://www.connectionstrings.com/

Jim

 

[mori0043]

Ok, I used this one:

Provider=sqloledb;Data Source=music;Initial Catalog=cadets;User Id=sa;Password=sa;

Here is my message: Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.

I can connect with query analyzer...

Any more ideas???

thanks in advance!!

 

[jbenson001]

http://support.microsoft.com/kb/889615/en-us

 

[mori0043]

All ready tried all of these steps and nothing worked... Any other ideas?

 

[jbenson001]

What is the front end you are using to connect? .NET?

 

[jbenson001]

try:

Server=[b]<Your Server>[/b];initial catalog=Cadets;User ID=sa;Password=sa;persist security info=True

 

[mori0043]

Still the same thing. I am using ASP to connect. I have another database running on the same machine with the exact same configuration and when I change servers to local then everything is fine....

Any other ideas?

Thanks in advance!

 

[jbenson001]

Do you have other dbs on the same Server, or other SQL Server instances on the same server?

 

[mori0043]

No just one. It seems that everything runs fine if I have the web server on the same machine as the DB, but once move the web server to another computer I always get the same error.

Ahhhh!! I am so stuck !!

I am running :

Machine 1: XP SP2 / windows SQL Server 2K
Machine 2: XP SP2 / IIS 6

Any other Ideas?

 

[unclerico]

I would try and create an ODBC connection on the Web server and see if you are able to connect to the remote database through that. If it works, try to alter your connection string in you .asp pages to use the ODBC connection. Probably not the most optimal solution but it is a different angle to go with in terms of troubleshooting.

 

[jbenson001]

Having the sql server on another server is the best setup and should not cause problems. You really have to check the connection string and make sure you are pointing to the correct sql server(name) in the connection string.

Server=<Your [b]SQL[/b] Server>;initial catalog=Cadets;User ID=sa;Password=sa;persist security info=True

 

[mori0043]

I would have thought that it should be the best setup... I have done this a million and never run into this problem. I am really not sure what else to do. I have changed the connection string a million different ways and nothing. I guess I can try ODBC, but this will not be an acceptable solution in the end...

 

[jbenson001]

I am stumped at this point. I will post if I come up with something. Let me know if you find the solution.

Jim

 

[mori0043]

Thanks... I will certainly post the solution when I find it. There must be someone else out there with a suggestion?

Help please

 

[unclerico]

There must be someone else out there with a suggestion?

I understand that the ODBC connection is not acceptable, but that is why I stated to use it as another method in your troubleshooting process. I'm with you, I don't use ODBC connections either, but when I am beating my head against the wall trying to elminate possible causes of errors I try it just to verify that I am in fact referencing the correct server and am using the correct login information

 

[oakgrove]

If I understand you try to connect from an asp page to an sqlserver where the web server and the sqlserver are not on the same machine and it give you:

"Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection."

If you put the sqlserver on the same box as the web server you can connect.

If this is the case (and because the error message suggests it) it would seem to be a permission issue.

Does it have something to do with the security context of the web server? (I am assuming IIS) Would the SQLServer need to be set to allow the "IUSER_webservername" account to have permissions on the database etc.

Sorry if this is way off the track but you wanted another idea.

Oakgrove Computer Grouper
Lansing, MI

 

[travisbrown]

First things first.

Can you actually ping the SQL Server from the web server?

 

[Welshbird]

I've had this same issue and it is to do with the IUSER_ user which is connecting to SQL.

I found it to be the mixed security with IIS using Windows and SQL expecting SA user. My solution was to make the IIS connect to SQL using windows security.

Let me know if that helps - I don't work there anymore now so I can;t check the settings! But I do remember the issue. May this at least points you in teh right direction.



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[mori0043]

travisbrown: I can ping the server...

willif: If it has to do with IUSER_ user connecting to SQL Server then what can I do? I cannot use windows security. I can only connect through sql server security.

oakgrove: I will try to add IIS_servername as a user in SQL Server to see what happens but I am pretty sure that I all ready tried this. I might have overlooken something though...

UPdate
----
I am now at a client site with the same web server machine and when I change my asp ole db connection string to a client site database everything works with no problems. I am seriously confused. Maybe the settings are a lot different on the SQL Server here...?