How to Create SQL Azure Database Users With Microsoft Access VBA

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

Return to Top

By Patrick Wood | Also posted in Access, Access Downloads, Access Tips, Church Management Software, Cloud Computing, database, free downloads, Microsoft Access, SQL Azure, SQL Azure Developer Portal, VBA, Windows Azure | Tagged Access, Access Downloads, Access Tips, database, Free Access Downloads, Microsoft Access, Software, SQL Azure, SQL Server, VBA, Windows Azure | Comments (0)

Microsoft Access DSN-Less Linked Tables: TableDef.Append or TableDef.RefreshLink?

When it came to creating DSN-Less Linked Tables I had always used a procedure that deleted the TableDef and appended a new one until a problem occurred. The code I was using to save Linked Tables as DSN-Less Tables was not working with some of the Views in SQL Azure. This was a serious problem because the application I was developing would be distributed to clients who would then distribute it to their clients. We did not want to use a DSN file. But now the code that normally worked without a hitch was failing.

Because I was developing for SQL Azure, I had to use SQL Azure Security which includes the Username and Password in the Connection string. Even though I explicitly set the dbAttachSavePWD (Enum Value: 131072) when I appended the new TableDefs the Connection Property of my views still did not include my Username and Password. So I quickly wrote some code to loop through the TableDef properties to see if I could discover the problem.

Sub ListODBCTableProps()

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim prp As DAO.Property
    
    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
        If Left$(tdf.Connect, 5) = "ODBC;" Then
            Debug.Print "----------------------------------------"
            For Each prp In tdf.Properties
                'Skip NameMap (dbLongBinary) and GUID (dbBinary) Properties here
                If prp.Name <> "NameMap" And prp.Name <> "GUID" Then
                     Debug.Print prp.Name & ": " & prp.Value
                End If
            Next prp
        End If
    Next tdf
    
    Set tdf = Nothing
    Set db = Nothing
    
End Sub

I discovered that the TableDef Attributes of the Views for which my code was not working was 536870912 but for the Tables and Views that were working it was 537001984. After checking the TableDefAttributeEnum Enumeration values I was puzzled. The Attributes value for the Views which were not working was 537001984 which is the value for dbAttachedODBC (Linked ODBC database table). And the value of the Attribute for the Tables and Views that were working was 536870912 which is not in the list. After a few moments I figured it out. I saw that if you add the dbAttachedODBC value of 536870912 to the dbAttachSavePWD value of 131072 it equals the 537001984 Attributes value of the DSN-Less Tables and Views that were set properly. This made sense since the documentation Description for dbAttachSavePWD is “Saves user ID and password for linked remote table”. Apparently the Views needed both Attributes. But how could I set it?

Even though my code explicitly set the Attributes value to dbAttachSavePWD when creating the new TableDefs it was not working. Eventually I found some code that used the TableDef.RefreshLink Method, added the TableDefs Attributes dbAttachSavePWD (131072) value, and tested it. This solution worked. Below is the code I used.

Function SetDSNLessTablesNViews() As Boolean

     Dim db As DAO.Database
     Dim tdf As DAO.TableDef
     Dim strConnection As String

     SetDSNLessTablesNViews = False 'Default Value

     Set db = CurrentDb

     'Use a Function to get the Connection string
     'Note: In actual use I never use "Connection" in my Variables or Procedure names.
     'I disguise them to make it hard for a hacker to use code to get my Connection string
     strConnection = GetCnnString()

     'Loop through the TableDefs Collection
     For Each tdf In db.TableDefs
         'Verify the table is an ODBC linked table
         If Left$(tdf.Connect, 5) = "ODBC;" Then
             'Skip System tables
             If Left$(tdf.Name, 1) <> "~" Then
                 Set tdf = db.TableDefs(tdf.Name)
                 tdf.Connect = strConnection
                 If tdf.Attributes < 537001984 Then
                     tdf.Attributes = dbAttachSavePWD 'dbAttachSavePWD = 131072
                 End If
                 tdf.RefreshLink
             End If
         End If
     Next tdf

     SetDSNLessTablesNViews = True

     Set tdf = Nothing
     Set db = Nothing

End Function

I felt better about using the tdf.RefreshLink Method rather than deleting the TableDefs and appending them again. I read that you could delete your TableDefs and not be able to append a new one if there is an error in the Connection string at this page on Doug Steele’s website at the bottom of the page.

I found an interesting discussion about whether to delete and then append a new TableDef or use the RefreshLink Method on Access Monster. However the latest Developer’s Reference documentation settles the matter for me when it states the TableDef.RefreshLink Method “Updates the connection information for a linked table (Microsoft Access workspaces only).”

You may also want to see the sample code from The Access Web by Dev Ashish using the RefreshLink Method.

Below is an example of the code used to get the Connection string. As I stated in the procedure notes I never use “Connection” in Constants, Variables, or Procedure names. Nor do I use cnn, con, cnnString, etc. Instead I disguise the name of my Procedure to make it hard for a hacker to use to get my Connection string. Constants and Procedure names, along with some variables, are easily seen by opening up even an accde or mde file with a free Hex editor unless you have encrypted the database file. If I can see the name of your Constant I can very easily get its value.

'Don't forget to change the name of this procedure
Function GetCnnString() As String

     GetCnnString = "ODBC;" _
         & "DRIVER={SQL Server Native Client 10.0};" _
         & "SERVER=MyServerName;" _
         & "UID=MyUserName;" _
         & "PWD=MyPassW0rd;" _
         & "DATABASE=MySQLDatabaseName;" _
         & "Encrypt=Yes"

End Function

You can see or download the code used in this article from our Free Code Samples page.

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

How To’s for Microsoft Access and SQL Azure with Code

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

Return to Top

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&nbspUser
    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

Return to Top

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

Return to Top

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.

Return to Top

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