Open-SqlConnection not working with -Credential

[Seth401]

Hi there,

running Open-SQLConnection -Server server.example.com\instance -Database dummy works. The SQL connection is opened, and I am able to use Invoke-SqlQuery. Adding -Credential $creds results in a login error. Using the same $creds I am able to use Start-Process to successfully log in and query the database.

Would you happen to have an idea why this is acting differently? Might this be related to a two hop problem or windows authentication in some way?

• Works: Open-SQLConnection -Server server.example.com\instance -Database dummy
• Doesn't work: Open-SQLConnection -Server server.example.com\instance -Database dummy -Credential $creds
• Works: Start-Process -Credential $creds -ArgumentList '-command "Open-SQLConnection -Server server.example.com\instance -Database dummy"'

Looking at the full stack trace for the error reveals it's reported as Error Number: 18456, State:1, Class: 14.

[mithrandyr]

@Seth401 -- the credentials are used for accessing the server using SQL Credentials. When you don't provide credentials, then you are authenticating by windows Auth. You can't just pass your windows Auth credentials in a credential object and have it work.

So if you are WindowsUser-A and you need to access the database as WindowsUser-B, then you will need to impersonate WindowsUser-B or run a new process as WindowsUser-B in order to authenticate as that user.