ASP-DB Connection

Try this:

Provider=SQLOLEDB;Data Source=serverip;User ID=username;Password=password;Initial Catalog=dbname;"

Does it still error?

Yes unfortunately, same error.

OK. What version of SQL Server?

SQL Server 2000
Windows 2003
IIS6

it works fine with SQL Server 7, Windows 2000, and IIS5

Hi

Take a look at

http://www.connectionstrings.com/

cheers

Len

Sure the username and password is still valid for the SQL Server 2000 box? Can you ping the SQL Server's IP address from the web server? Is the SQL Server running (sorry, you have to ask these questions sometimes, please don't take offence)?

Have you upgraded your MDAC as of late? It typically comes with SQL Server service packs, but sometimes, the update doesn't happen. Go to:

http://www.microsoft.com/downloads/results.aspx?productID=&freetext=mdac&DisplayLang=en

and download MDAC 2.8.

I've found that doing this solves a lot of connection problems. Earlier versions of the MDAC don't recognize OLE DB connection strings.

GoofyDawg

Thanks GoofyDawg, but I installed the MDAC 2.8 and same error.  

Yes the SQL is running, because it works for different sites and databases.

And muzzy2003 no offense taken.

Good. :)

Does it work for different sites on the same web server connecting to different databases on the same SQL server? Are you sure the login you are using actually has permissions on the database you are trying to access?

Yes on different webpages, it works to connect to a seperate database on the same SQL Server.  Under SQL Enterprise Manager both users are listed in the Users in the seperate databases.  

Well, this is getting me stumped. Can you copy and paste a working connection string from another page and yours into a reply to this? Just XXX out the password and, if you really feel you need to, the first octet of the IP address.

Have you tried using the older connection string method... pre-OLEDB? I've found that sometimes that works:

Provider=MSDASQL; Driver={SQL Server}; Server=server_name_or_address; Database=database_name; UID=username; PWD=password;

This is a DSN-less connection. Also, have you looked at the rights you've assigned to your user accounts?

GoofyDawg

Const CONNECTION_PROVIDER   = "SQLOLEDB"

Const CONNECTION_SERVER     = "xxx.xxx.xxx.xxx"
Const CONNECTION_UID        = "username"
Const CONNECTION_PW         = "password"
Const CONNECTION_DATABASE   = "dbname"
Const ADO_CONNECTION_CLASS = "ADODB.Connection"

Function GetOpenConnection()
      Dim objConn
      Set objConn = Server.CreateObject(ADO_CONNECTION_CLASS)
      
      objConn.ConnectionString = "PROVIDER=" & CONNECTION_PROVIDER &_
                                     ";SERVER=" & CONNECTION_SERVER &_
                                     ";UID=" & CONNECTION_UID &_
                                     ";PWD=" & CONNECTION_PW &_
                                             ";DATABASE=" & CONNECTION_DATABASE      
      
      objConn.open
      Set GetOpenConnection = objConn
End Function


And GoofyDawg the rights are set up as public, db_owner.

Gary Thanks for your help, I followed those instructions, and it gave me a working connection string !  

Here's what worked...

Provider=SQLOLEDB.1