Accessing SQL on a different port number (classic ASP connection string)

Question

I've got an old classic ASP site that connects to a local sql server 2000 instance. We're moving the db to a new box, and the port is non standard for sql (out of my control). .NET connection strings handle the port number fine by adding it with ,1999 after the server name/IP. The classic ASP connection string isn't working with the same syntax. I checked connectionstrings.com and couldn't find one that worked.

Current connection string (set to an Application variable in Global.asa):

Driver={SQL Server};Server=xxx.xxx.xxx.xxx;Database=dbname;Uid=dbuser;Pwd=dbpassword

I've installed the SQL Native Client and couldn't get that working either (still working on this)

Any ideas?

Answer 1

I think we need more information. What are you using to connect to the database? ODBC? OLE DB? Are you connecting through and ODBC DSN? Is the connection string in your ASP code, or is your data access via a VB or COM DLL?

Answer 2

cst = "Provider=SQLOLEDB;" & _  
        "Data Source=<x.x.x.x>,<port number>;" & _  
        "Initial Catalog=<dbname>;" & _  
        "Network=DBMSSOCN;" & _  
        "User Id=<uid>;" & _  
        "Password=<pwd>"  

    set conn = CreateObject("ADODB.Connection")  
    conn.open cst

More info

Answer 3

The solution was installing the SQL Native Driver from MS, then updating the connection string to the following:

Driver={SQL Native Client};Server=xxx.xxx.xxx.xxx,port;Database=dbname;Uid=dbuser;Pwd=dbpassword

I originally couldn't get it working with the SQL Native Client because of a firewall issue that was later resolved.