We help IT Professionals succeed at work.

Can't connect via OLEDB to SQL Server running RDS

gglover
gglover asked
on
Is there something wrong with the following:

sConn = "Provider=SQLOLEDB.1;"
sConn = sConn & "Data Source=GSVR\SQL2000;"
sConn = sConn & "Initial Catalog=DBAX2;"
sConn = sConn & "User ID=usrBob;Password=frustration;"

Set m_ds = CreateObject("rds.dataspace")

Set m_df = m_ds.CreateObject("rdsserver.datafactory", "http://myurl")

strsql = "SELECT fld1, fld2 FROM tblSomeTable"

Set rs1 = m_df.query(sConn, strsql)
==============================================
????????????????????????
==============================================
I get an error on the last line:
Error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

The SQL SVR is on the same machine as the webservice.  

I have tried various combinations.  The SQL Server name is correct, it does exist, the username/pw is correct, the user has permissions on the DB....
I've tried both NT and the NT+SQL SVR security settings.
I've tried using IUSR_GSVR as the user.
I've tried using just the "SQL2000" part of the DB Name as shown in Enterprise Mgr....
I've tried other formats as far as the connection string goes...
Oh yeah - if I use a simple "DSN=AXDB2", it works.  I wanted a DSN-less connection, though as I understand it's better from a performance point of view.

Any solutions come to mind?

Many thanks -

Comment
Watch Question

Commented:
check into using application roles on sql svr.  it's a better solution than compiling usernames and passwords into your source code.

Author

Commented:
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?  

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

Commented:
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;"
     

Author

Commented:
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...
CERTIFIED EXPERT
Expert of the Year 2014
Top Expert 2014

Commented:
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
Commented:
PAQ'ed and points refunded

Explore More ContentExplore courses, solutions, and other research materials related to this topic.