locked
MSAccess AAD Authentication Errors RRS feed

  • Question

  • I am experiencing the difficulty with MS Access database application linking to Azure SQL database with AAD authentication. While the same credential does not have any issue with SSMS.

    The error I get from MS Access trying to setup DSN is;

    Could some one help?

    Thanks.

    Thursday, November 30, 2017 10:45 PM

All replies

  • By any chance did you provide the database name in the connection string from Access?

    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Friday, December 1, 2017 9:26 AM
  • Hello!

    I have explicitly specified the database name.

    Driver={ODBC Driver 13 for SQL Server};Server=tcp:[azure_sqlserver].database.windows.net,1433;Database=[my_database];Uid={your_user_name};Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword

    Basically copied connection string from Azure Portal.

    Saturday, December 2, 2017 2:53 AM
  • Hi, I tried to do it at my end and was able to connect without any issue. Below are the steps followed - 

    Open ODBC datasources from the machine you are connecting -> Under User DSN Tab -> Click Add -> In Driver Selection use ODBC Driver 13 for SQL Server -> Click Finish -> Given Name (e.g. MSAccessDSN, Description, and Server Name -> Click Next -> Choose Option '
    With Active Directory Password Authentication using  a login ID and password entered by User.' -> Provide AAD User Name and Password -> Click Next -> Click Finish -> Click Test Data Source

    You must see Test Connection Successful and MSAccessDSN in User Data Sources List.

    Then go to MS Access, under External Data -> New Data Source -> From Databases -> From Azure database -> Import Option -> Click Ok -> Under Machine Data Source Tab -> Select MSAccessDSN -> Ok -> Provide AAD User Name and Password -> Click Ok

    Please let us know if you still see an issue.


    Saturday, December 2, 2017 5:50 AM
  • Hello!

    I followed your manual setting method and got the following error on "Test Data Source"

    Microsoft ODBC Driver for SQL Server Version 14.00.1000
    
    Running connectivity tests...
    
    Attempting connection
    [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
    
    TESTS FAILED!

    I just cannot figure out why it complains with 'NT AUTHORITY\ANONYMOUS LOGON' though username and password are given.

    Thanks.

    Saturday, December 2, 2017 6:12 AM
  • Can you run through this troubleshooting guide and let us know how it goes?

    https://support.microsoft.com/en-us/help/10085/troubleshooting-connectivity-issues-with-microsoft-azure-sql-database

    On the first tab, click Go to the next pane to select the detailed error messages and then select Login failed for user <User name>. A list of options will appear.

    Wednesday, December 6, 2017 11:34 PM
  • First, sorry my bad english. It was a headacke but I solved with ADO:

    -------------------------------------------------------------------------
    1. The structure of tables in Access an SQL Azure are the same;  the access tables are empty and only are usefull to build Forms
    -------------------------------------------------------------------------
    2. Using Microsoft Studio SQL server 2014:
       2.1 I defined a new user applicative in new query of "Master" data base as:

              Create Login <MyUserLogin> WITH Password = '<mypasswordAzure>'

       2.2 I defined a new user in in new query of  <MyDataBase>, associated to previus Login:

             Create User <MyOperationUser> FROM Login <MyUserLogin>

             Exec sp_addrolemember 'db_datawriter' , '<MyOperationUser>'

             Exec sp_addrolemember 'db_datareader' , '<MyOperationUser>'

           Note: With this conditions this user only can read, insert, delete and update 

    -------------------------------------------------------------------------
    3. Specifying connection to Azure:
        3.1   In a Module (That a called "Variables" but you can call it as you whish) :

    Public ADOcnn As ADODB.Connection
    Public SQL1 as String
    Public WOK as Boolean

       3.2  In other Module (that I use for general routines):

    Public Sub ADOConexion()           
      Set ADOcnn = New ADODB.Connection
      ADOcnn.ConnectionString = driver={SQL Server};Server=tcp:<mysuscription>.database.windows.net,1433;Database=<mydatabase>;User ID=<MyUserLogin>@<mysuscription>;Password=<mypasswordAzure>;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
      ADOcnn.open
    End Sub

    -------------------------------------------------------------------------
    4. Parameter to execute Insert, Delete and Update en Azure, where you'll need (sub, funtion, etc.):
       4.1  Prepare your query: 

    WOK = True
    WSql1 = "<your query for insert, delete or update>"
    RoutineExecuteQuery
    If WOK then
        ....  
    Else
          RoutineErrorConnOrSQL            'your criteria
    End If

        4.2  Routine of execution of your query using the variable SQL1:

    Public Sub RoutineExecuteQuery()
    Dim ADOcmd As New ADODB.Command

    WOK = True
    On Error Resume Next
    ADOConexion
    If WOK Then
        Set ADOcmd.ActiveConnection = ADOcnn
        ADOcmd.CommandType = adCmdText
        ADOcmd.CommandText = WSql1
        Set rs = ADOcmd.Execute
        
        If Err.Number <> 0 Then
            MsgBox Err.Number
            WOK = False
        End If
    End If
    Set ADOcmd = Nothing
    Set ADOcnn = Nothing

    End Sub


    -------------------------------------------------------------------------
    5. Fill Forms with query using SQL1 as well, with my general routine:
       (is important that I don't pass variable to procedure, I define all my general variable in a Module)

      5.1 Prepare your query where items have the same order that you will use in your Form)
       (the varibale that I use retrieve information, but the most important is the variable:  WFormOrigen ) 

    WSqL1 = (SELECT ..... FROM ...  WHERE ....)
    WFormOrigen = "<my form name>"
    WSWCountRecods = False
    WSWLastNum = False
    WCont = 0
    WLong = 0
    WString2 = ""
    WStringF = ""
    WDate2 = Date

    RoutSelectADO
    If Not WOK Then
          RoutineErrorConnOrSQL            'your criteria
    End If


      5.2  General routine for fill Forms:

    Public Sub RutSelectADO()
    Dim ADOrst As New ADODB.Recordset

    WCont = 0
    On Error GoTo Err_Rutina
    WOK = True
    ADOConexion
    If WOK Then
        Set ADOrst.ActiveConnection = ADOcnn
        ADOrst.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        ADOrst.LockType = adLockReadOnly
        ADOrst.CursorLocation = adUseClient
        ADOrst.CursorType = adOpenKeyset
        ADOrst.Source = WSql1
        ADOrst.Open
        
        WSiHay = True
        WCont = ADOrst.RecordCount
        If ADOrst.EOF Then
            If WFormOrigen <> "" Then
                WLong = 0
                Set Forms(WFormOrigen).Recordset = ADOrst
            End If
            WSiHay = False
        Else
            If WSWCountRecods Then
            Else
                If WSWLastNum Then
                    WSWLastNum = False
                    ADOrst.MoveFirst
                    If IsNumeric(ADOrst(WString2)) Then
                        WLong = ADOrst(WString2)
                    Else
                        If IsDate(ADOrst(WString2)) Then
                            WDate2 = ADOrst(WString2)
                        Else
                            WStringF = ADOrst(WString2)
                        End If
                    End If
                Else
                    If WFormOrigen <> "" Then
                        WLong = ADOrst.RecordCount
                        Set Forms(WFormOrigen).Recordset = ADOrst
                    End If
                End If
            End If
        End If
    End If

    Exit_Rutina:
        Set ADOrst = Nothing
        Set ADOcnn = Nothing
        Exit Sub

    Err_Rutina:
        WOK = False
        Resume Exit_Rutina

    End Sub

    -------------------------------------------------------------------------
    I hope this may help you.

    Regards  JJ Durán
    Mexico


    Thursday, December 7, 2017 8:41 PM