CODEWITHSUNDEEP
sqlsql-serverexcelpowershell
Glowie
Glowie

Reputation: 2309

Password disappears from ODBC Data Source Administrator

I created a user data source based on

http://blog.mclaughlinsoftware.com/2012/09/12/sql-server-odbc-osn/

When I try to invoke a powershell script that sends SQL data to EXCEL

$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN", $ws.Range("A1"), $SQL)

if ($qt.Refresh()){
    $ws.Activate()
    $ws.Select()
}

The EXCEL spreadsheet appears and prompts me for the SAME password that I entered when creating the DSN

enter image description here

And when I go to ODBC Data Source Administrator, select the DSN I created, click "Configure", and keep going "Next", it shows password field is blank

enter image description here

How to I fix this so 1. Password remains in DSN 2. I am not prompted for password everytime I run Powershell script that sends SQL data to EXCEL

Upvotes: 0

Views: 2018

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46231

You need to specify the user id and password in the application connection in order to avoid the prompt. The SQL Server ODBC drivers do not store user credentials. I believe you can just specify the credentials in the connection string:

$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=YourLogin;PWD=YourLoginPassword", $ws.Range("A1"), $SQL)

Note that you should never use the sa login for routine application data access.

Upvotes: 1

Related Questions