Powershell connection string to SQL server

Question

Hi I'm trying to execute a Powershell on my local PC however using data from a remote SQL server.

I have script like this. It's wrong because I haven't stated the username and password to log on to the server. Could anyone tell me the correct format of doing that?

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=10.10.1.34;Database=AMSDataWarehouseTest;user id=YQBGantt;password=123yqbgantt"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "YQBreport1"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$SQLResult =$DataSet.Tables[0]
$commands = $SQLResult | foreach-object -process { $_.output }> output.ps1
.\output.ps1

How do you know its wrong? If there is an error please tell us what it is. (I can see you have included the user name and password so that is clearly not the issue.) — Richard
– Richard, Commented Feb 25, 2013 at 11:40
Hi Richard, The username and password is for the database, not for the server. to be able to use the database on the server I think I need to include the log in to the server as well in the connection string somehow. — Apriljuly
– Apriljuly, Commented Feb 25, 2013 at 12:26
No you don't need to login to the server and then to SQL Server. You just log into SQL Server. — Richard
– Richard, Commented Feb 25, 2013 at 12:30
I tried with the connection string I have, it's not working, however, it's not throwing any error as well, the powershell window will just remain open all the time. Normally I access the server through VPN, would that be the problem? — Apriljuly
– Apriljuly, Commented Feb 25, 2013 at 12:38
In that case switch on tracing and find out where it is hanging (it might not be on connection, maybe that command is hanging): Set-PSDebug -Trace 1. NB. if the connection was failing due to authentication you would get an immediate error. — Richard
– Richard, Commented Feb 25, 2013 at 13:25

Cristian Lupascu · Accepted Answer · 2013-02-25 11:08:44Z

2

Try using this connection string instead:

Data Source=10.10.1.34;Initial Catalog=AMSDataWarehouseTest;uid=YQBGantt;pwd=123yqbgantt

Also, make sure the SQL Server is configured to accept remote connections.

answered Feb 25, 2013 at 11:08

Cristian Lupascu

40.8k18 gold badges107 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2063329 · Accepted Answer · 2015-06-15 03:48:29Z

0

I have a SQL Server instance 2012 named 'RegencyPark' on a laptop named 'Hodentek8'. These statements connect to my SQL Server and render it to open.

PS C:\Users\Jayaram> $ConnectionString="Server=Hodentek8\RegencyPark;database=AdventureWorks2012;trusted_connection=true;"

PS C:\Users\Jayaram> $Connection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)

PS C:\Users\Jayaram> $Connection.Open()

PS C:\Users\Jayaram> $Connection.State
Open

answered Jun 15, 2015 at 3:48

user2063329

4572 gold badges5 silver badges15 bronze badges

Collectives™ on Stack Overflow

Powershell connection string to SQL server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related