43 out of 78 rated this helpful - Rate this topic

How to: Connect to Windows Azure SQL Database Using ADO.NET

Using ADO.NET to connect to a database in Microsoft Windows Azure SQL Database is similar to connecting to an instance of SQL Server on your premises. This topic describes a few considerations for connecting to Windows Azure SQL Database and provides an example console application that you can use to get started. For more general information about connecting to a data source with ADO.NET, see Connecting to a Data Source (ADO.NET).

Considerations

When connecting to a database in SQL Database with an ADO.NET application, consider the following points:

Avoid injection attacks by using the SqlConnectionStringBuilder class. It is available from the .NET Framework to simplify creating the connection string. For more information, see Connection String Builders (ADO.NET).
A connection string presents a potential vulnerability. For more information about protecting your connection string, see Protecting Connection Information (ADO.NET).
Encrypt your connection, especially when connecting to SQL Database over the Internet, so that a man-in-the-middle attack is not possible. Set the Encrypt connection property to True (Encrypt = True). Set the TrustServerCertificate connection property to False (TrustServerCertificate = False).
For general considerations about connecting to databases in SQL Database, see Guidelines for Connecting to Windows Azure SQL Database.
When opening a connection to a SQL Database, set the connection timeout to 30 seconds.

Known Issue When Connecting to SQL Database Using ADO.NET in .NET 4.0 and Earlier

The following error message is used in SqlClient versions older than .NET 4.5:

Step Name : Read Query on Instance Management DB \n System.Data.SqlClient.SqlException (0x80131904): The instance of SQL Server you attempted to connect to does not support encryption.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() 
at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)

This error indicates a failure during the login process, and is not an encryption failure. Beginning in .NET 4.5, ADO.NET displays a better error message:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

Console Application Example

The following example is a console application that connects to Windows Azure SQL Database. The console application performs the following steps:

The console application uses a SqlConnectionStringBuilder object to connect to the master database in Windows Azure SQL Database. The application then creates a database whose name comes from the sampleDatabaseName string.
Then, the console application uses a second SqlConnectionStringBuilder object to connect to the new database.
Once connected, the console application uses the second SqlConnectionStringBuilder object to create a database table and perform a number of data manipulation language (DML) statements.
Finally, the console application returns data to the console before and after data changes.

Using this Example

To use this example in Visual Studio with your SQL Database server, do the following steps:

Open Visual Studio and create a console application.
Replace the code in the program file with the code from this example.
Replace <ProvideUserName> with the name of an Windows Azure SQL Database login that has been assigned the dbmanager role. Note: if you use the login@server username format, the server portion of the name must match the first part of the server's fully qualified name. For example, if your server is servername.database.windows.net, your login name is loginname@servername. For more information about Windows Azure SQL Database roles, see Managing Databases and Logins in Windows Azure SQL Database.
Replace <ProvidePassword> with the password associated with the login. Note: We recommend using a strong password when creating a login. For more information, see Strong Passwords.
Replace <ProvideServerName> with the fully qualified domain name of your SQL Database server. For example: servername.database.windows.net
Replace <ProvideDatabaseName> with the name of the database you want the code to create.
Run the code.

Note
There are no Transact-SQL statements in this code to remove the new database. Delete the database before running this application again.


Option Explicit On
Option Strict On

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data.SqlClient
Imports System.Data

Module Program
    ' Provide the following information
    Private ReadOnly userName As String = "<ProvideUserName>"
    Private ReadOnly password As String = "<ProvidePassword>"
    Private ReadOnly dataSource As String = "<ProvideServerName>"
    Private ReadOnly sampleDatabaseName As String = "<ProvideDatabaseName>"


    Sub Main()
        ' Create a connection string for the master database
        Dim connString1Builder As New SqlConnectionStringBuilder()
        With connString1Builder
            .DataSource = dataSource
            .InitialCatalog = "master"
            .Encrypt = True
            .TrustServerCertificate = False
            .UserID = userName
            .Password = password
        End With

        ' Create a connection string for the sample database
        Dim connString2Builder As New SqlConnectionStringBuilder()
        With connString2Builder
            .DataSource = dataSource
            .InitialCatalog = sampleDatabaseName
            .Encrypt = True
            .TrustServerCertificate = False
            .UserID = userName
            .Password = password
        End With


        ' Connect to the master database and create the sample database
        Using conn As New SqlConnection(connString1Builder.ToString())
            Using command As SqlCommand = conn.CreateCommand()
                conn.Open()

                ' Create the sample database
                Dim cmdText As String
                cmdText = String.Format("CREATE DATABASE {0}", _
                                        sampleDatabaseName)
                command.CommandText = cmdText
                command.ExecuteNonQuery()
                conn.Close()
            End Using
        End Using


        ' Connect to the sample database and perform various operations 
        Using conn As New SqlConnection(connString2Builder.ToString())
            Using command As SqlCommand = conn.CreateCommand()
                conn.Open()

                ' Create a table
                command.CommandText = "CREATE TABLE T1(Col1 int primary key, Col2 varchar(20))"
                command.ExecuteNonQuery()

                ' Insert sample records
                command.CommandText = "INSERT INTO T1 (col1, col2) VALUES (1, 'string 1'), (2, 'string 2'), (3, 'string 3')"
                Dim rowsAdded As Integer = command.ExecuteNonQuery()

                ' Query the table and print the results
                command.CommandText = "SELECT * FROM T1"
                Using reader As SqlDataReader = command.ExecuteReader()
                    ' Loop over the results
                    While reader.Read()
                        Console.WriteLine("Col1: {0}, Col2: {1}", _
                                          reader("Col1").ToString().Trim(), _
                                          reader("Col2").ToString().Trim())
                    End While
                End Using

                ' Update a record
                command.CommandText = "UPDATE T1 SET Col2='string 1111' WHERE Col1=1"
                command.ExecuteNonQuery()

                ' Delete a record
                command.CommandText = "DELETE FROM T1 WHERE Col1=2"
                command.ExecuteNonQuery()

                ' Query the table and print the results
                Console.WriteLine("After update/delete, the table has these records...")
                command.CommandText = "SELECT * FROM T1"
                Using reader As SqlDataReader = command.ExecuteReader()
                    ' Loop over the results
                    While reader.Read()
                        Console.WriteLine("Col1: {0}, Col2: {1}", _
                                          reader("Col1").ToString().Trim(), _
                                          reader("Col2").ToString().Trim())
                    End While
                End Using

                conn.Close()
            End Using
        End Using

        Console.WriteLine("Press Enter to continue...")
        Console.ReadLine()
    End Sub
End Module

Community Additions

ADD