Connect to SQL Server Database from PowerShell

Question

I have looked around online for a while now and found many similar problems but for some reason I can t seem to get this working   I am just trying to connect to a SQL server database and output the query results to a file - See PowerShell script below  What I am uncertain about is how to integrate the User ID and Password into the connection string    SQLServer    aaaa database windows net   SQLDBName    Database   uid   john   pwd    pwd123   SqlQuery    SELECT   from table    SqlConnection   New-Object System Data SqlClient SqlConnection  SqlConnection ConnectionString    Server    SQLServer  Database    SQLDBName  Integrated Security   True  User ID    uid  Password    pwd    SqlCmd   New-Object System Data SqlClient SqlCommand  SqlCmd CommandText    SqlQuery  SqlCmd Connection    SqlConnection  SqlAdapter   New-Object System Data SqlClient SqlDataAdapter  SqlAdapter SelectCommand    SqlCmd  DataSet   New-Object System Data DataSet  SqlAdapter Fill  DataSet    DataSet Tables 0    out-file  C  Scripts xxxx csv    The following error message is received   Exception calling  Fill  with  1  argument s    Windows logins are not supported in this version of SQL Server

User · Answer

database Intraction   SQLServer    YourServerName   use Server Instance for named SQL instances   SQLDBName    YourDBName   SqlConnection   New-Object System Data SqlClient SqlConnection  SqlConnection ConnectionString    Server    SQLServer  Database    SQLDBName   User ID  YourUserID  Password  YourPassword    SqlCmd   New-Object System Data SqlClient SqlCommand  SqlCmd CommandText    StoredProcName   SqlCmd Connection    SqlConnection   SqlAdapter   New-Object System Data SqlClient SqlDataAdapter  SqlAdapter SelectCommand    SqlCmd   DataSet   New-Object System Data DataSet  SqlAdapter Fill  DataSet    SqlConnection Close      End  database Intraction clear

User · Answer

I did remove integrated security     my goal is to log onto a sql server using a connection string WITH active directory username   password   When I do that it always fails   Does not matter the format     sam company user     upn whatever company com     basic username

User · Answer

Change Integrated security to false in the connection string   You can check verify this by opening up the SQL management studio with the username password you have and see if you can connect open the database from there  NOTE  Could be a firewall issue as well

User · Answer

Integrated Security and User ID   Password authentication are mutually exclusive  To connect to SQL Server as the user running the code  remove User ID and Password from your connection string    SqlConnection ConnectionString    Server    SQLServer  Database    SQLDBName  Integrated Security   True     To connect with specific credentials  remove Integrated Security    SqlConnection ConnectionString    Server    SQLServer  Database    SQLDBName  User ID    uid  Password    pwd

User · Answer

Assuming you can use integrated security  you can remove the user id and pass    SqlConnection ConnectionString    Server    SQLServer  Database    SQLDBName  Integrated Security   True

User · Answer

The answer are as below for Window authentication    SqlConnection   New-Object System Data SqlClient SqlConnection  SqlConnection ConnectionString    Server  SQLServer Database  SQLDBName Integrated Security True

[sql-server] Connect to SQL Server Database from PowerShell

Examples related to sql-server

Examples related to powershell

Examples related to connection-string