Automatically create ODBC DSN connection with special port and password. Add-OdbcDsn cmdlet

Question

_{Wednesday, December 31, 2014 9:06 AM}

Hi,

I first posted a question in the SQL forum but I'm posting it here instead because its a Powershell question.

In a non-persitent VDI enviroment we are trying to automatically create a ODBC DSN connection to a SQL server.

We are using Windows 8.1 so we also have Powershell 4 together with the add-odbcdsn cmdlet. 

But when trying to add set -SetPropertyValue for network port different than default and a password we get an error.

here is the command:

Add-OdbcDsn -Name test -DriverName "SQL Server" -DsnType User -SetPropertyValue @("PWD=test", "SERVER=10.0.0.1")

and here is the error message:

Add-OdbcDsn : Attempt to set the {UID or PWD} key of a DSN. These keys should not be stored in the registry for securit
y reason. Provide the credential information at runtime via SQLDriverConnect, SQLConnect or SQLBrowseConnect.
At line:1 char:1
+ Add-OdbcDsn -Name test -DriverName "SQL Server" -DsnType User -SetPropertyValue @ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (MSFT_OdbcDsnTask:Root/Microsoft/...SFT_OdbcDsnTask) [Add-OdbcDsn], Cim
   Exception
    + FullyQualifiedErrorId : MI RESULT 4,Add-OdbcDsn

NB!:  this command does not contain a port number, but when adding it without a password(PWD string) we just the default port.

All replies (5)

_{Wednesday, December 31, 2014 2:41 PM ✅Answered}

For help creating DSNs you should post in SQLServer forum.

You cannot store credentials in a registry based DSN by default.

PortNumber is not part of the ODBC conenction.  It is part of the client configuration settings.

¯\(ツ)_/¯

_{Thursday, January 1, 2015 2:43 PM ✅Answered}

Hi Primeid,

Agree with Jrv, we cannot store  UID and PWD in an ODBC datasource, For example, you can create a DSN using the user interface but if you look at the DSN stored in the registry the UID and PWD are not stored.

ODBC it is always required when you connect using a DSN that the caller supply UID and PWD if they want to use standard login during connection time. 

These similar discussion are for your reference:

Creating ODBC DSN for SQL Native Client fails for not-integrated authentication

is user name and password required in ODBC admin / User DSN?

In addition, to read data from a SQL Server database using an ODBC DSN with SQL Authentication via powershell, please refer to this script:

Open SQL database with ODBC DSN and SQL AuthenticationIf there is anything else regarding this issue, please feel free to post back.

If you have any feedback on our support, please click here.

Best Regards,

Anna Wang

TechNet Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Support, contact [email protected]

_{Thursday, January 8, 2015 1:42 AM}

Hi Primeid,

I‘m writing to check if the suggestions were helpful, if you have any questions, please feel free to let me know.

If you have any feedback on our support, please click here.

Best Regards,

Anna

TechNet Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Support, contact [email protected]

_{Friday, April 28, 2017 12:46 PM}

When the ODBC system DSN is created manually the UID and PWD (encrypted) are certainly stored in the registry:

Check the registry.  Look for the ODBC.INI for the DSN created. I cannot post a screenshot here or I would

_{Friday, April 28, 2017 12:59 PM}

When the ODBC system DSN is created manually the UID and PWD (encrypted) are certainly stored in the registry:

Check the registry.  Look for the ODBC.INI for the DSN created. I cannot post a screenshot here or I would

This is only true if the provider allows it.  Currently only MSAccess allows this via ODBC.  SQLServer does not allow it but you can add the password via direct registry edits for some providers.

\(ツ)_/