Member Avatar for FallenPaladin

Hi, I am hoping someone will kindly help me out. I am new to ASP and need to create a connection to an SQL server 2005 database.

I have failed misserably at web so far and is my worst subject on the course that i am doing. I understand that I must set the conection string and I am unable to utilise DSN due to the security of the servers and the need to transport the site and database between locations.

Therefore I need to write the connection string from scratch. However each time I try to connect to the DB I have errors. Could someone please provide a sample SQL server connection string please.

Thanks for reading Jon

  • 2 Contributors
  • 1 Reply
  • 123 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by techtix

Dani AI

Generated

Following 's request for a DSN-less Classic ASP connection (and 's pointer to external resources), here are concise, working connection-string examples and a short troubleshooting checklist geared to SQL Server 2005.

' Classic ASP (VBScript) - SQL Server auth
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=SERVERNAME\INSTANCE;Initial Catalog=DatabaseName;User ID=dbuser;Password=dbpassword;"
' Classic ASP (VBScript) - Windows auth (must run under an account with DB rights)
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=SERVERNAME\INSTANCE;Initial Catalog=DatabaseName;Integrated Security=SSPI;"
' Classic ASP (VBScript) - explicit TCP port
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=tcp:db.example.com,1433;Initial Catalog=DatabaseName;User ID=dbuser;Password=dbpassword;"

Troubleshooting checklist:

  • Verify SQL Server allows remote connections and TCP/IP is enabled (use SQL Server Configuration Manager). See Microsoft guidance: .
  • For named instances, start SQL Server Browser or specify the instance port explicitly.
  • Open firewall ports (default 1433 or the instance port).
  • If not in the same domain, prefer SQL authentication; Windows auth requires correct IIS identity or a domain account and possible SPN/Kerberos configuration.
  • Test connectivity from the web server with Management Studio or sqlcmd to separate network issues from code errors.

Simple ADO error check:

If Err.Number <> 0 Then
  Response.Write "ADO error: " & Err.Number & " - " & Err.Description
End If

Security notes: avoid using the sa account; store credentials outside the web root and lock file system permissions.

Member Avatar for techtix

If you ever need a connection string, the greatest resource is Carl Prothman's site. Formerly Able Consulting.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.