Home / server / Questions / 31195
Accepted
Brett Veenstra
Asked: 2009-06-25 11:05:59 +0800 CST

How to login with SQL Authentication using PowerShell

  • 772

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

sql-server-2008 powershell

2 Answers

  1. 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"
    • 5
  2. Best Answer

    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".

    • 4