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.
Get the free Demonstration Application that shows how effectively Microsoft Access can use SQL Azure as a back end.
More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
Free Church Management Software with Contributions management.
Code Samples
Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.
Happy computing,
Patrick (Pat) Wood
Gaining Access
http://gainingaccess.net
