Can Access web page connected to SQL Server

[JordanR]

Hello, I am a newbie at SQL Server upgrading from MS Access
I am having trouble accessing a ASP that connects to my SQL Server db. On my testing environment I can access it fine but when I set up a DSN on a different server for the SQL Server database on the test server I get the following error message:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'myusername'. Reason: Not associated with a trusted SQL Server connection.


Can anyone direct me here?

 

[Paladine]

What are you doing exactly when you get this message? Accessing your ASP Page?

Or creating the DSN?

_________________
Paladine

"Doing what others find difficult is talent.....

 

[JordanR]

I am accessing the asp page.
The DSN has been created on the remote server and tested Successfully.

 

[01310564]

Try using "sa" as the user name rather than your user name and leave the password blank.

Hugh

 

[jeepxo]

The first step i would take is go to ODBC databases in the control panel or administrative tools on that server.

Drill down to the ODBC entry for that database and use the "test connection" from there. I will bet that you can't connect to the database that way.

That error message means you are trying to connect to the database using the IUSER account with NTAthentication.

You will need to change the setup of the database so that you can connect to it with a SQL account. The default as Hugh has pointed out is "sa" with no password, but that leaves you open to a huge security hole.

You will want create an SQL account and password, then change the database so that you can connect to it through NTAuthentication and SQL Authentication.


"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[JordanR]

How do I create a SQL account? Would that be the same as creating a user account on the Server(machine)? Or is that something deep within SQL Server?

 

[JordanR]

Hey All, I finally got it working
I got my book today, "Mastering SQL Server 200" and it directed me through the steps to create SQL server login as jeepxo mentioned. After that I was able to successfully connect using that login. Also, being that I was trying to connect to server via an ASP document from totally different server, I had to use a different connection string.

"Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=databasename;" & _
"User ID=loginname;" & _
"Password=loginpassword"

I got the connection string from:

http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMySQL

and under sub section

To connect to SQL Server running on a remote computer (via an IP address)

Thank you for your help.

 

[JordanR]

link correction

http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer