How to login with SQL Authentication using PowerShell

Question

I'm trying to connect via SQL Server Powershell Cmdlet (read not ADO.NET) using SQL-based Authentication, not Windows Integrated security.

Anyone know the format to use for this?

• Server: foo
• Instance: DEFAULT
• UserID: homer
• Password: simponsrock

Edit:
I can get to this SQL-cmdlet path using SQL Server Management Studio.

When doing it that way, the path is:
SQLSERVER:\SQL\foo\DEFAULT+homer

The problem is that I cannot set-location to this path via PowerShell

Answer 1

Have a look here: http://msdn.microsoft.com/en-us/library/cc281947.aspx.

Your issue is dealt with in the section "Managing SQL Server Authentication Connections".

Answer 2

You can also do a one-off using the Invoke-Sqlcmd statement. You have to have the SQL 2008 Powershell host installed for this (sqlps.exe) which gets installed with SQL 2008.

PS C:\> sqlps
PS>invoke-sqlcmd -query "SELECT * FROM sys.databases" -serverinstance "foo" -username "homer" -password "simpsonsrock"