Why do we need a SQL Azure Database User Account? An Access Database cannot access SQL Azure Objects such as Tables, Views, or Stored Procedures without one. That is, unless you use your SQL Azure Administrator account which would be living very dangerously if you were distributing your database to others. So before your database can do anything with SQL Azure, a Database User Account must be created that it can use. Also permissions must be granted to use the necessary SQL Azure Tables, Views, and Stored Procedures. We are going to show you how you can use a pass-through query in Access to create SQL Azure Database Users using Access VBA.
Database users must be created in the database in which they will exist because the “USE” statement can only work for the current database in SQL Azure. So to create a Database User we must use a query that runs in the Database in which we want to create the User. And since it would be confusing, to me at least, to log in using “JoeDeveloper” and work as Database User “SamCodeSlinger” my normal practice is to create a Database User with the same name as a Login Name.
If we were using SQL Server Management Studio (SSMS) or the Windows Azure Management Portal we could create a Database User as shown with the following Transact-SQL (T-SQL):
CREATE USER MyLoginName FOR LOGIN MyLoginName
Or:
CREATE USER MyLoginName FROM LOGIN MyLoginName
But you can easily create Database Users with Microsoft Access using the following two procedures, passing the Login Name to the CreateSQLAzureDBUser Function:
'Example usage: Call CreateSQLAzureDBUser("MyLoginName")
Public Function CreateSQLAzureDBUser(strLoginName As String) As Boolean
On Error GoTo ErrHandle
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
CreateSQLAzureDBUser = False 'Default Value
strSQL = "CREATE USER " & strLoginName & " FOR LOGIN " & strLoginName
'Create the Database User
Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
'Change obfuscatedFunctionName to the name of a Function
'that Returns your SQL Azure Database Connection String
qdf.Connect = obfuscatedFunctionName
qdf.SQL = strSQL
qdf.ReturnsRecords = False
qdf.Execute dbFailOnError
'If no errors the Database User was Created
CreateSQLAzureDBUser = True
ExitHere:
'Cleanup for security and to release memory
On Error Resume Next
Set qdf = Nothing
Set db = Nothing
Exit Function
ErrHandle:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
& vbCrLf & "In procedure CreateSQLAzureDBUser"
Resume ExitHere
End Function
The Database User must be created in the Database in which it is to be used and not in the master Database. You can do this by changing MySQLAzureDatabaseName in the obfuscatedFunctionName Function to the name of the database in which you want to create the users.
'It is best to change the name of this procedure for better security for your use.
'The strIn Argument value, "Wb_gR%/PD\-k&yZq~j>l", is used like a Password to keep
'unauthorized users from getting your Connection String. You should also change it
'to suit you before you use it in a distributed application.
Public Function obfuscatedFunctionName(strIn As String) As String
If strIn = "Wb_gR%/PD\-k&yZq~j>l" Then
obfuscatedFunctionName = "ODBC;" _
& "DRIVER={SQL Server Native Client 10.0};" _
& "SERVER=tcp:MyServerName.database.windows.net,1433;" _
& "UID=MyUserName@MyServerName;" _
& "PWD=MyPassword;" _
& "DATABASE=MySQLAzureDatabaseName;" _
& "Encrypt=Yes"
Else
obfuscatedFunctionName = vbNullString
End If
End Function
For better security you can keep the Login Name, Password, and User Name hidden in the code without exposing it to the Access user.
We now have the ability to create Database Users but we still need to Grant Permissions before our Access Database can use this User Account to access any data in SQL Azure. We plan to show how you can do that in the articles to come.
You can download the code used in this article from our Free Code Samples page.
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
