ADO Dababase connection on different server

[Cineno]

Hello,

My workplace has a site on one server with an SQL database. They have another site on a different server, which they now want to have content generated from the 1st site's database.

Pages hosted on the first site's hosting account were connecting to the database through this:

set db=Createobject("Adodb.Connection")
set rs=CreateObject("Adodb.Recordset")
db.ConnectionString="PROVIDER=SQLOLEDB.1;Uid=userID;pwd=Password;Initial Catalog=databaseName;Data Source=serverAddress"
db.open

I tried to put that on the new site in hopes that it would be able to connect to the database on the other server, but I'm getting "SQL Server does not exist or access denied" messages.

Anyone have any idea what I can do? Thanks for any help.

 

[foxbox]

That connection string is trying to connect to a SQL server on a server "serverAddress", using user "userID" with password "Password".
So the obvious question is if the SQL server address is right? It is not common to have the SQL and webserver on the same machine.
Next comes the SQL Server security. Windows/SQL/mixed security? SQL security: does the user exist? Password oke? Access granted to "databaseName"?
Is the different server in your domain? If yes: do you have access to the SQL Enterprise Manager? If you connect, you'll see a drop down list with all servers in the domain...

 

[Cineno]

I'm sure that the address, user name and passwords are correct. The connection code I copied directly from a current page that accesses the database, and pasted it into the new page.

The different server is not under the same domain. Sorry I don't have too much other info, I'm kind of new to this whole process.

Is there anything I'd have to change in the connection string for it to find the data source? I figured it would all just be the same from the page I copied it from.

 

[MzKitty]

We handle our connections thru ODBC. If we want to connect to the Test SQL Server, then I would have an ODBC that points at that server. When an app goes live, I would change the ODBC to point at the production SQL server. We try to get away from hard coding the server name into the actual applications.