add-odbcDSN for Azure database with Encrypted=yes

Question

Hey!

Posting this in this forum as well as on Powershell forum, it relates to both components I believe.

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 

Answer 1

Add-OdbcDsn -Name "DSNNAME" -DriverName "ODBC Driver 17 for SQL Server" -DsnType "User" -SetPropertyValue 
@("Server=servername.database.windows.net", "Trusted_Connection=Yes", "....Cannot use Authentication option with Integrated Security option.

Cannot use Authentication option with Integrated Security option."

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.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

You're natural genius. 

Thanks!

Olek