Visual Basic Classic
Visual Basic Classic
Microsoft SQL Server 2008
Microsoft SQL Server 2008

Connect vb6 app to remote db

I am trying to connect my vb6 app to either a local or a remote database, depending on which is selected. Here is my connection string:

    If sWhichSrvr = "Remote" Then
        conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CCMeet;User ID=id;Password=pwd;Data Source=209.185.199.1,1433\SQLEXPRESS"
    Else
        conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CCMeet;UID=id;Pwd=pwd;Data Source=VIRA-5\SQLEXPRESS"
    End If

Open in new window

I get a "server not found" error when I try to connect. Is there an error in my string or my process?

Bob SchneiderCo-OwnerAsked: 2013-01-05

Who is Participating?

LVL 84

Dave BaldwinFixer of ProblemsCommented: 2013-01-05

Sqlbrowser.exe always runs on port 1434 as far as I know. Allowing Remote connections is a checkbox on the Properties for the server instance (top of the list). SQL authentication is set up for a user in the Properties for their Login when it is created. And under the Properties for the database you're using, you may have to setup permissions for that login.

LVL 84

Dave BaldwinFixer of ProblemsCommented: 2013-01-05

SQLOLEDB is an old driver. The preferred and current driver for SQL Server 2008 is the SQL Server Native Client 10.0 which can be downloaded here: http://www.microsoft.com/en-us/download/details.aspx?id=16978 Look for Microsoft® SQL Server® 2008 R2 Native Client in the middle of the page and pick the version for your computer.

On this page are connection strings for using SQLNCLI10: http://www.connectionstrings.com/sql-server-2008

SQL Server must also be setup for TCP/IP remote access using SQL authentication. If you are trying to access it using 'named instance' like 209.185.199.1,1433\SQLEXPRESS or VIRA-5\SQLEXPRESS, the Sqlbrowser.exe service must also be running on port 1434.

Bob SchneiderCo-OwnerAuthor Commented: 2013-01-05

Good information. Sqlbrowser.exe (I assume that is SQL Server browser in the "Services" area?) is running but I can't see how to check the port it is running on? TCP/IP is enabled but how do I check if it is using SQL authentication?