SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using Windows stored credentials to connect to SQL in containers

I work with SQL Server in containers pretty much exclusively when testing code and one of my real bug bears is that SQL Server in containers does not support Windows authentication (unless you’re using Windocks).

So when I’m working I find it quite annoying to have to specify a SA username & password when I want to connect.

OK, I can use Get-Credential, assign to a variable, and then reference that in a connection string but I want something a bit more permanent especially as I always use the same password for all my containers (shoot me, it’s local dev ?? )

What I’ve setup on my laptop is a stored credential using the CredentialManager powershell module.

Here’s how it works, first I create the credential: –

Import-Module CredentialManager

New-StoredCredential -Target "SqlDocker" -UserName "sa" -Password "Testing1122" -Persist LocalMachine

The -Persist LocalMachine allows me to reference this credential in other sessions as the default scope is session only. I can check this in another session by running: –

Get-StoredCredential -Target "SqlDocker"

So now run a container (using the same credentials as stored above): –

docker run -d -p 15789:1433 `
    --env ACCEPT_EULA=Y `
        --env SA_PASSWORD=Testing1122 `
            --name testcontainer microsoft/mssql-server-linux:latest

And now use the credential to connect to the container. I’m going to drop it into the dbatools Connect-DbaInstance cmdlet to pull information back about the SQL instance within the container: –

# set credential to variable
$cred = Get-StoredCredential -Target "SqlDocker"

# connect to Sql using credential
$srv = Connect-DbaInstance 'localhost,15789' -Credential $cred
    $srv.Edition
    $srv.HostDistribution
    $srv.HostPlatform
    $srv.Version

Boom! Connected to the SQL instance within the container using the stored credential. No more fudging passwords when typing out commands ??

Thanks for reading!

The DBA Who Came In From The Cold

I’m Andrew Pruski and I am a SQL Server DBA with 5 years experience in database development and administration.

The online SQL Server community has helped me out immensely throughout my career, whether from providing reference material in blog posts, or answering my (sometimes obscure) questions on forums. So, to try and say thank you, I would like to contribute my own experiences in the hope that they could benefit someone out there.

So here’s my general ramblings and thoughts about working as a SQL Server DBA.

You can find me on twitter @DBAFromTheCold

If you have any feedback on my blog please send me an email to dbafromthecold@gmail.com.

Comments

Leave a comment on the original post [dbafromthecold.com, opens in a new window]

Loading comments...