Here are some How To’s to help get you started with SQL Azure. Some of them can also help with SQL Server.

• How to Create Logins in SQL Azure
• How to Create Database Users in SQL Azure
• How to Build an ODBC Connection String in Access
• How to Migrate a SQL Server Database to SQL Azure by Generating Scripts

How to Create Logins in SQL Azure

Create Logins Using SQL Server Management Studio (SSMS):

Logins must be created in the master database. To create a Login in SSMS I normally right click on the server and select “New Query” which opens a blank query in the master database. It must be run from the master database because you cannot use “USE master” with SQL Azure.

T-SQL:

CREATE LOGIN MyLoginName WITH password = 'zX/w3-q7jU'
GO

To be secure Passwords must follow the required Password Policy. It is best to use Strong Passwords. They must be at least 8 characters long and contain at least one number or special character such as -/~^&. Since ODBC connection strings utilize the characters []{}(),;?*!@ they should not be used in Passwords.

Create Logins Using Microsoft Access:

You can create Logins with Microsoft Access with VBA using an unnamed temporary Pass-through QueryDef, which is a technique recommended in the Microsoft Access Developer References. You can use the following sample code, passing the Login Name and the Password to the Function:

'Example usage: Call CreateLogin("MyLoginName", "zX/w3-q7jU")

Function CreateLogin(strLoginName As String, strPW As String) As Boolean
On Error GoTo ErrHandle

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String

    strSQL = "CREATE LOGIN " & strLoginName & " WITH password = '" & strPW & "'"

    Set db = CurrentDb

    'Create the Logins
    Set qdf = db.CreateQueryDef("")
    qdf.Connect = GetConnMaster 'Function to get Connection string to master database in SQL Azure
    qdf.SQL = strSQL
    qdf.ReturnsRecords = False

    qdf.Execute dbFailOnError

    'If no errors the Login was Created
    CreateLogin = True

ExitHere:
    'Cleanup for security and to release memory
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Exit Function

ErrHandle:
    CreateLogin = False
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
    & vbCrLf & "In procedure CreateLogin"
    Resume ExitHere

End Function

How to Create Database Users in SQL Azure

Create Database Users With SQL Server Management Studio (SSMS):

Database users must be created in the database in which they will exist and usually the Login Name is used as the Database User Name.

T-SQL:

CREATE USER MyLoginName FOR LOGIN MyLoginName
GO

Or:

CREATE USER MyLoginName FROM LOGIN MyLoginName
GO

Create Database Users With Microsoft Access:

You can create Database Users with Microsoft Access using the following sample code, passing the Login Name to the Function:

'Example usage: Call CreateDBUser("MyLoginName")

Function CreateDBUser(strLoginName As String) As Boolean
On Error GoTo ErrHandle

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String

    strSQL = "CREATE USER " & strLoginName & " FOR LOGIN " & strLoginName

    'Create the Database User
    Set db = CurrentDb

    Set qdf = db.CreateQueryDef("")
    qdf.Connect = GetConnDB 'Function GetConnDB gets Connection string to the database
    qdf.SQL = strSQL
    qdf.ReturnsRecords = False

    qdf.Execute dbFailOnError

    'If no errors the Database User was Created
    CreateDBUser = True

ExitHere:
    'Cleanup for security and to release memory
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Exit Function

ErrHandle:
    CreateDBUser = False
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
    & vbCrLf & "In procedure CreateDBUser"
    Resume ExitHere

End Function

How to Build an ODBC Connection String in Access

For instructions and examples on how to build and use an ODBC Connection string in VBA see my article Building Safer SQL Azure Cloud Applications with Microsoft Access

How to Migrate a SQL Server Database to SQL Azure by Generating Scripts

Generate a Database Script for SQL Azure from the Journey to SQL Authority with Pinal Dave blog. A very helpful article that shows how to build a SQL Azure database by generating scripts from an existing SQL Server database.

In smaller databases it may be practical to also Migrate the Data by Generating Scripts using SSMS.

How to Migrate Both Schema and Data

1) Migrate the Schema.

I recommend that you first follow the example in the article and migrate the schema first. That will give you the opportunity to make any changes that might be needed. As shown in the article you click on the “Advanced” button, followed by clicking on “Script for the database engine type”, and then selecting “SQL Azure Database”. Then click on “Types of data to script” and select “Schema only”.

2) Migrate the Data.

As before you click on the “Advanced” button and “Script for the database engine type” and then select “SQL Azure Database”. But this time select “Data only” for the “Types of data to script”. This may produce a large script depending upon the amount of data stored in the database.