ADODB connection don't connect to sqlserver Expresss

1
votes

I have installed an instance of MS SQL server Express on a new server. I have a web application with some old ASP.NET code written in VBScript, using another database (SQL Server) which works fine.

every attempt to connect gives the same error:

Microsoft OLE DB Provider for SQL Server error '80004005' [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

The code is:

 Set udb = Server.CreateObject("ADODB.Connection")     
'SQLEXPRESS ENVIRONMENT
 udb.Open("Provider=SQLOLEDB; Data Source=SEHAN5134AS1\SQLEXPRESS,1433; Initial Catalog=testDB; User ID=myUser; Password=sEcret;")
'PRODUCTION ENVIRONMENT
'udb.Open("Provider=SQLOLEDB; Data Source=manifolds-avalon.db.teliasonera.net,1433; Initial Catalog=smallworldweb_user-data; User ID=myUser; Password=sEcret;")

I have made an ODBC connection to verify that the the web server is able to connect to the server with the SQL-server and it works fine. I have tried to give the myUser any kinds of rights on the database. I can connect to the database with other programs (tcpip and named pipes are allowed, remote connections are etc.)

I have tried to add 

"Server=SEHAN5134AS1.db.teliasonera.net; Network=DBMSSOCN;"

to the connection string. I have tried to use the full server name in the "Data Source" string. But now I have no ideas how to solve the problem. Any suggestions?

EDIT I have tried to make the connection via powershell as proposed by Dan Guzman and the result is shown below with the result of setting the ODBC connection. All results are from the same web server. Results of using powershell and ODBC setup

4
sql-servervbscriptasp-classic
Is SEHAN5134AS1 your local pc or is it a remote machine on your lan? – Steve
It's a remote machine – Anders Finn Jørgensen
Did you try to stop the local firewall? – Steve
@MutedDisk. No. – Anders Finn Jørgensen
@Steve. port 1433 is open on the webserver. It also works on the ODBC connection. – Anders Finn Jørgensen

4 Answers

1
votes

Try this code

udb.Open("Provider=SQLOLEDB; Data Source=SEHAN5134AS1\SQLEXPRESS; Initial Catalog=testDB; User ID=myUser; Password=sEcret;")
1
votes

goto MS SQL server config and enable sql server browser by changing start mode from disabled to automatic then start the service. you should be able to connect after. make sure your ports are open 1433 TCP and 1434 UDP on your MS SQL server not IIS. Also make sure you have TCP/IP enabled in SQL server network config

MSSQL Server config

1
votes

I struggled with the same issue. And for me the solution was to "have TCP/IP enabled in SQL server network config" and remember also to restart the service for it to take effect!

Computer Management

0
votes

This answer is just to close the question. I never got the connection up working and installed a SQLSERVER Express on the webserver instead.