classic asp and sql database azure

Question

We are migrating to Azure and have one app that is still in classic ASP. We have migrated the database that it needs to communicate with to SQL database in Azure.

Looking for guidance or examples on how to get classic ASP to connect with SQL database.

Here's what we have now.

ConnString = "DRIVER=SQL Server Native Client 10.0;Server=servername.database.windows.net,1433;Database=azuredb;UID=user@serve‌ rname;PWD=password;Encrypt=yes;TrustServerCertificate=no;" 

Set Conn = CreateObject("ADODB.Connection") 
Conn.Open ConnString 
set rs=Server.CreateObject("ADODB.recordset") 
SQL = "SELECT * from table" 
rs.Open( SQL ) 

Connection String (for readability)

SQL Server Native Client 10.0;Server=servername.database.windows.net,1433;
Database=azuredb;UID=user@serve‌ rname;PWD=password;Encrypt=yes;
TrustServerCertificate=no;

With the above connection we get the following error

Microsoft OLE DB Provider for ODBC Drivers error '80004005' 
[Microsoft][ODBC Driver Manager] 
Data source name not found and no default driver specified

We are running this in a VM running Server2008 R2

Answer 1

The answer is that you must install the SQL native client.

http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409

Answer 2

Experienced the same issue. While installing the correct SQL native client as another answer mentions may be the ideal fix, the quick fix is to simply change the driver to "SQL Server".

For example from Driver={SQL Server Native Client 10.0}; to Driver={SQL Server};

In your case your connection string should be:

ConnString = "Driver={SQL Server};Server=servername.database.windows.net,1433;Database=azuredb;UID=user@serve‌ rname;PWD=password;Encrypt=yes;TrustServerCertificate=no;"

Answer 3

For classic ASP:

Set Con = server.createobject("ADODB.Connection") Con.Open "Provider=SQLOLEDB;Server=XXXXX.database.windows.net,1433;User ID=XXXXX;Password=XXXXX;Initial Catalog=XXXXX;"

you must change XXXXX to your infos.

Answer 4

use the following connection string

Driver={SQL Server Native Client 10.0};Server=tcp:[serverName].database.windows.net;
Database=myDataBase;Uid=[LoginForDb]@[serverName];Pwd=myPassword;Encrypt=yes;

see the link.

Answer 5

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Volta_Reporting_Application.DBL
{
    public class DBHandler
    {
        public SqlConnection _SqlConnection { get; set; }
        public String _SqlConnectionString { get; set; }
        public DataSet _DataSet { get; set; }
        public List<SqlCommand> _CommandList { set; get; }
        public DBHandler()
        {
            //_SqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; ;
            _SqlConnectionString = Helpers.Helper.GetConnectionString();
        }
        public bool OpenConnection()
        {
            
                _SqlConnection = new SqlConnection(_SqlConnectionString);
            if (SqlConnection != null && SqlConnection.State == ConnectionState.Closed)
            {
               
                _SqlConnection.Open();
            }
   .Open);
        }

        public bool CloseConnection()
        {
            if (SqlConnection != null && SqlConnection.State == ConnectionState.Open)
                _SqlConnection.Close();
            if (_SqlConnection != null)
                _SqlConnection.Dispose();
            return _SqlConnection == null;
        }
        public object ExecuteMyCommand(SqlCommand cmd) 
        {
            bool res = false;
            try
            {
                OpenConnection();
                cmd.Connection = _SqlConnection;
                if (cmd.ExecuteNonQuery() > 0)
                {
                    res = true;
                }
            }
            catch (Exception)
            {

                res = false;
            }
            finally 
            {
                CloseConnection();
            }
            return res;
        }

        public object CRUD(string Query, char operation = 'c')
        {
            operation = char.Parse(operation.ToString().ToLower());
            object res = null;
            try
            {
                OpenConnection();
                SqlDataAdapter da = new SqlDataAdapter();
                switch (operation)
                {
                    case 'c':
                    case 'i':
                        da.InsertCommand = _SqlConnection.CreateCommand();
                        da.InsertCommand.CommandText = Query;
                        da.InsertCommand.ExecuteNonQuery();
                        res = true; 
                        break;
                    case 'z':
                        da.SelectCommand = _SqlConnection.CreateCommand();
                        da.SelectCommand.CommandText = Query;
                        return da.SelectCommand.ExecuteScalar();
                    case 's':
                    case 'r':
                        DataSet ds = new DataSet();
                        da.SelectCommand = _SqlConnection.CreateCommand();
                        da.SelectCommand.CommandText = Query;
                        //da.SelectCommand.ExecuteReader();
                        da.Fill(ds);
                        res = ds;
                       //ds.Dispose(); 
                        break;
                    case 'u':
                        da.UpdateCommand = _SqlConnection.CreateCommand();
                        da.UpdateCommand.CommandText = Query;
                        res=da.UpdateCommand.ExecuteNonQuery();
                        break;
                    case 'd':
                        da.DeleteCommand = _SqlConnection.CreateCommand();
                        da.DeleteCommand.CommandText = Query;
                        da.DeleteCommand.ExecuteNonQuery();
                        res = true;
                        break;
                    default: break;
                }
                if (res == null)
                    res = false;
            }
            catch (Exception)
            {
                res = null;
            }
            finally
            {
                CloseConnection();
            }
             return res;
        }
    }
}

Answer 6

You will most likely need to use ODBC to achieve a connection. Details can be found on MSDN: http://msdn.microsoft.com/en-us/library/azure/hh974312.aspx