database connection string not working

Question

See code segment below..commented out is the original connection string that was in place for a local hosted AccessDB.  I am trying to move this app to azure and have migrated the db to MySQL and looking to get this thing connected.  do I need to put the password in ".."  The connection string is straight out of Azure so not sure why this is not working.  See error code after code segment.

<%

dim strConnString

'strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\busoft\data\busSoft.mdb"

'strConnString = "Driver=Microsoft Access Driver {(*.mdb,*.accdb)};DBQ=C:\busoft\data\busSoft.mdb"

strConnString = "Server=tcp:xaa0e4adkb.database.windows.net,1433;Database=bussoft;User ID=jjtoso@xaa0e4adkb;Password={password};Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"

set cnn = Server.CreateObject("ADODB.Connection") '  connects to database

cnn.Open strConnString   ' should open the database

Here is the error code I am getting

Driver error '80004005'

Device source name not found or no default driver specified

---

6fingers

Answer 1

Hi,

There is some of things I don't understand :

• you say you migrate your DB to MySQL, but work with an Azure SQL connection string
• you use a ADODB.Connection (ASP Classic ?) but you provide an ADO.Net Connection String

ADODB require another kind of connection string, look at the OLEDB Connection String for SQL Azure

https://www.connectionstrings.com/sql-azure/

if it's really MySQL look at

https://www.connectionstrings.com/mysql-oledb-mysqlprov/

Regards,

---

Yan Grenier

Merci de bien vouloir "Marquer comme réponse", les réponses qui ont répondues à votre question, et de noter les réponses que vous avez trouvé utiles.

Answer 2

Sorry for confusion

I am migrating to Azure Website.  The site is Classic ASP with a Access database back end (mdb file).  I migrated the database from access to Azure SQL using Microsoft SQL service migration assistant.    The database is safely in azure I can access it and run queries using azure sql tools.

My issue is I don't know how to connect and open the database from my classic asp site.  I don't have a lot of experience with classic asp...the legacy code I inherited is below.   

<%

dim strConnString

strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\busoft\data\busSoft.mdb"set cnn = Server.CreateObject("ADODB.Connection") '  connects to database

cnn.Open strConnString   ' opens the database...do some work.

I modified my code to set the  strConnString as azure suggests

strConnString = "Server=tcp:xaa0e4adkb.database.windows.net,1433;Database=bussoft;User ID=jjtoso@xaa0e4adkb;Password={password};Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"

How do I do a Server.CreateObject() and Open the database so I can continue.

---

6fingers

Answer 3

Hi,

I request you to open a support ticket (as it requires for us to look into the code in every possibility which might have sensitive information that cannot be shared over the public forum ) .

Regards,

Shirisha Paderu

Answer 4

Hi,

As per https://msdn.microsoft.com/en-us/library/azure/ee336282.aspx  below is not supported

• Windows Authentication.      Use SQL Server authentication in your connection string instead.
• OLE DB. However, you can connect to a      SQL Server linked server with an application written with SQL Server      Native Client OLE DB.
• Distributed transactions. For more information, see Azure      SQL Database General Guidelines and Limitations.

Please also check http://stackoverflow.com/questions/3085811/why-doesnt-microsoft-support-ole-db-connections-to-sql-azure for more details.

Regards,

Shirisha Paderu