Connect to SQL Server remotely using VBA

[mms180]

Hello everyone, I hope I am asking the right people, I have an SQL server setup locally and I connect to it using this code:
strConnection = "Driver={SQL Server};Server=PCNAME;Database=Master;Trusted_Connection=yes"
Set cnDTB = New ADODB.Connection
cnDTB.ConnectionString = strConnection
cnDTB.Open

This works fine when I am running the VBA code on the same machine, but when I try to connect to it via a remote machine, I get this error when it tries to connect

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied

any tips will help, I am a beginner with SQL

 

[dk87]

Try this format for your connection string:

"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseName;Data Source=ServerName"

Where the Initial Catalog is your database name and your Data Source is your server name.

 

[mms180]

thank you dk87 I'm not sure I have the SQL server configured correctly, do I need to create an ODBC connection to the SQL server on the remote machine before I can connect to it? I tried that code on the remote machine and a similar error occurred

 

[dk87]

You don't need an ODBC connection configured, but you do need to make sure the SQL server driver is installed on the client machine.

 

[mms180]

I found an SQL server 2000 Driver for JDBC, is this the correct driver?

 

[dk87]

Use the latest MDAC install from Microsoft, it will have the correct driver.

 

[mms180]

dk87, It doesn't seem to work whenever I try to connect, using this command:

Dim cnDTB as ADODB.Connection

strConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=ServerPCName"
Set cnDTB = New ADODB.Connection
cnDTB.ConnectionString = strConnection
cnDTB.Open

the following errors appears:
[DNETLIB][ConnectionOpen (Connect()).]SQL SQL Server does not exist or access denied

I'm not sure what else to try...

 

[dk87]

1. I wouldn't recommend creating tables in the master database, create a new database.
2. As a test, try using the server ip address rather than the name, in case you have WINS/DNS issues.
3. You are trying to use domain authentication. Make sure that the user that is connecting to the database has access to do so.

 

[dk87]

Oh, sorry, I didn't notice something...I gave you bad info...remove the .1 from your provider, make it SQLOLEDB

 

[mms180]

thanks for all of your help dk87, it doesn't want to work, it seems like I am having issues with security. how do I make sure that the user on the remote machine has access to the database?

the remote machine hasa local user logged in, I added a user in the server with the same user name and login. is this the proper way to do this?
also, when I try to create an odbc connection on the remote machine I cannot connect to the sql server even though I can see it. if this part of the problem I am having?

 

[dk87]

The way you have it set up now it is going to try to use your NT credentials. If you want to use SQL logins, use the following:

Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=username;Initial Catalog=master;Data Source=ServerPCName

You will need to create a way for the user to enter their credentials, then use variables to pass them to your connect string (in the User ID and Password areas).

Even using this method, you need to make sure the user has permission to access the table. The easiset way to do this is to go into SQL Enterprise Manager, double-click on the table, and grant permissions to the user.

 

[mms180]

thansk dk87, one other thing i forgot to mention that might be significant, I do not have the actual SQL software running, I have MSDE running on the PC, is this a problem? does MSDE not allow remote access to the database?

 

[dk87]

Yes, that is exactly what your problem is. MSDE is a runtime product only for a single workstation.

 

[mms180]

sorry about all of the trouble, I appreciate all of your help, i'm gonna give our client a quote for SQL 2000.

before, I only had the local station use MSDE, and not until recently did the issue of a remote user needing access to the database did this issue arise. but thank you very much for all of your help.