Can't connect via OLEDB to SQL Server running RDS

Yeah, I do need to research more into application roles, server roles, database roles, cinnamon rolls, etc. ...
The basic idea here is that there's one user id that we're using for testing - anyone that would log on would use the same id/pw.
At any rate, is the application role a better solution with regard to being able to connect, or "just" from a security point of view?  

If the server is not on the same machine then verify that you're using the TCP net library, not the Named Pipes.

PROVIDER=SQLOLEDB;DATA SOURCE=machine_name;USER ID=user_name;PASSWORD=password;DATABASE=database_name;


1. provider: i don't know why you add ".1" at the end of SQLOLEDB
2. datasource: isn't "GSVR\SQL2000" an invalid machine name ?? Just use the name of the machine, that is all.
3. Initial Catalog ?? I just use DATABASE

Here is the correct one from

http://www.able-consulting.com/ADO_Conn.htm


Standard Security:

oConn.Open "Driver={SQL Server};" & _
                   "Server=MyServerName;" & _
                   "Database=myDatabaseName;" & _
                   "Uid=myUsername;" & _
                   "Pwd=myPassword;"

For Trusted Connection security:

oConn.Open "Driver={SQL Server};" & _
                   "Server=MyServerName;" & _
                   "Database=myDatabaseName;" & _
                   "Uid=;" & _
                   "Pwd=;"

' or

oConn.Open "Driver={SQL Server};" & _
                   "Server=MyServerName;" & _
                   "Database=myDatabaseName;" & _
                   "Trusted_Connection=yes;"

To Prompt user for username and password

oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};" & _
                   "Server=MyServerName;" & _
                   "DataBase=myDatabaseName;"
     

Turns out the Connection string was fine;  it was a permissions issue - I had to make user settings at the Server, database and even table levels.  

Could've also opened up other cans of worms, but for now it works.  I guess I need additional info sources for webserver / Win2000 / SQL Svr 2000 user and security configurations as they apply to HTTP use...

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Deleted
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
GaryC123
EE Cleanup Volunteer