none
add-odbcDSN for Azure database with Encrypted=yes RRS feed

  • Question

  • Hey!

    Posting this here and on SQL Server Data Access forum, I believe it relates to both. 

    I'm trying to create user dsn for Azure DB. The script I'm using right now is:

    Add-OdbcDsn -Name "DSNNAME" -DriverName "ODBC Driver 17 for SQL Server" -DsnType "User" -SetPropertyValue @("Server=servername.database.windows.net", "Trusted_Connection=Yes", "Database=dbname", "Authentication=ActiveDirectoryIntegrated", "Encrypt=Yes")


    While this command ends with success, I can't connect using this datasource, I'm getting the following error:

    Exception calling "Open" with "0" argument(s): "ERROR [FA001] [Microsoft][ODBC Driver 17 for SQL Server]Cannot use Authentication option with Integrated Security option.
    ERROR [FA001] [Microsoft][ODBC Driver 17 for SQL Server]Cannot use Authentication option with Integrated Security option."

    The way to fix the DSN is to open the ODBC data source Admin, click on the datasource, then configure, then go through the wizard without any single change. Looks like wizard is writing some properties under the hood. 

    I tried to compare properties before and after, and here is what I got:

    BEFORE THE WIZARD: 

    Get-OdbcDsn  -Name DSNNAME | select -expandproperty attribute
    
    Name                           Value
    ----                           -----
    TrustServerCertificate         Yes
    Database                       dbname
    Server                         servername.database.windows.net
    Trusted_Connection             Yes
    Authentication                 ActiveDirectoryIntegrated

    AFTER THE WIZARD:

    Get-OdbcDsn -Name DSNNAME | select -expandproperty attribute Name Value ---- ----- KeystoreSecret Encrypt Yes ClientCertificate Database dbname Server servername.database.windows.net TrustServerCertificate Yes KeystoreAuthentication KeystorePrincipalId KeystoreLocation Authentication ActiveDirectoryIntegrated

    As you can notice, I have few properties added: 

    • KeystoreSecret
    • ClientCertificate
    • KeystoreAuthentication
    • KeystorePrincipalId
    • KeystoreLocation

    but their values are not shown. How can I set them? These must be some system/user default as I don't see any corresponding input on the ODBC GUI. 

    Could someone advice what I miss here?

    Regards 

    Olek 


    Thursday, July 2, 2020 12:42 PM

Answers

  • the answer from Olaf Helper:


    The property "Trusted_Connection" means to logon using ADS Windows Authentication, the alias is "Integrated Security" as in the error message.

    SQL Azure DB don't support this authentication; remove the property and lookup the right connection string in Azure portal.

    Problem is solved now.

    • Marked as answer by olek.stasiak Thursday, July 2, 2020 1:44 PM
    Thursday, July 2, 2020 1:44 PM

All replies

  • the answer from Olaf Helper:


    The property "Trusted_Connection" means to logon using ADS Windows Authentication, the alias is "Integrated Security" as in the error message.

    SQL Azure DB don't support this authentication; remove the property and lookup the right connection string in Azure portal.

    Problem is solved now.

    • Marked as answer by olek.stasiak Thursday, July 2, 2020 1:44 PM
    Thursday, July 2, 2020 1:44 PM
  • The answer is basic to all DSN connections.  You cannot use both types of authentication in any connection string and AzureDB cannot use "Integrated Security" (Trusted Connection).


    \_(ツ)_/

    Thursday, July 2, 2020 10:29 PM
    Moderator