I am using docker-compose to create containers for my Go Server, and SQL Server database.

I need to create a username and password for the go server to connect to the SQL Server database. I've followed this guide, which has been successful so far (I can create the database, and run the init.sql file, and connect to it).

However, the challenge I'm facing is this: how can I move this password to an .env file or something similar where it is not stored as plain text?

Dockerfile

FROM mcr.microsoft/mssql/server:2019-GA-ubuntu-16.04
ENV ACCEPT_EULA y
ENV SA_PASSWORD MyFancyPassword123
COPY ./init.sql . 
COPY ./entrypoint.sh . 
EXPOSE 1433
CMD /bin/bash ./entrypoint.sh

entrypoint.sh:

#!/bin/bash

# Run init-script with long timeout - and make it run in the background
/opt/mssql-tools/bin/sqlcmd -S localhost -l 60 -U SA -P "MyFancyPassword123" -i init.sql &

# Start SQL Server
/opt/mssql/bin/sqlservr

init.sql:

CREATE DATABASE myapp;
go

USE myapp;
go

CREATE SCHEMA [enum]
GO

CREATE TABLE enum.attribute
(
    -- Primary Key
    ID_auto INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    
    -- System Information
    acronym VARCHAR(1700),
    summary VARCHAR(1700),
    comment VARCHAR(1700),

    -- Constraints & Foreign Keys
    CONSTRAINT DUPE_FILTER_enum_attribute_acronym UNIQUE(acronym),

    -- Local history columns
    modified_by   VARCHAR(1700),
    modified_date DATETIME2,
    created_by    VARCHAR(1700),
    created_date  DATETIME2,
)
GO

CREATE LOGIN myapp_login WITH PASSWORD='MyFancyPassword123', DEFAULT_DATABASE=myapp;
go

CREATE USER myapp FOR LOGIN myapp_login WITH DEFAULT_SCHEMA=dbo;
go

ALTER ROLE db_owner ADD MEMBER myapp;
go

docker-compose.yml:

services:
    mssql:
        build: .
        container_name: my-mssql-container
        ports:
            - "1433:1433"
        volumes:
            - ./init.sql:/init.sql
            - ./entrypoint.sh:/entrypoint.sh
        command: /bin/bash ./entrypoint.sh
        networks:
            - mssql-network

networks:
    mssql-network:
        driver: bridge

I am using docker-compose to create containers for my Go Server, and SQL Server database.

I need to create a username and password for the go server to connect to the SQL Server database. I've followed this guide, which has been successful so far (I can create the database, and run the init.sql file, and connect to it).

However, the challenge I'm facing is this: how can I move this password to an .env file or something similar where it is not stored as plain text?

Dockerfile

FROM mcr.microsoft/mssql/server:2019-GA-ubuntu-16.04
ENV ACCEPT_EULA y
ENV SA_PASSWORD MyFancyPassword123
COPY ./init.sql . 
COPY ./entrypoint.sh . 
EXPOSE 1433
CMD /bin/bash ./entrypoint.sh

entrypoint.sh:

#!/bin/bash

# Run init-script with long timeout - and make it run in the background
/opt/mssql-tools/bin/sqlcmd -S localhost -l 60 -U SA -P "MyFancyPassword123" -i init.sql &

# Start SQL Server
/opt/mssql/bin/sqlservr

init.sql:

CREATE DATABASE myapp;
go

USE myapp;
go

CREATE SCHEMA [enum]
GO

CREATE TABLE enum.attribute
(
    -- Primary Key
    ID_auto INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    
    -- System Information
    acronym VARCHAR(1700),
    summary VARCHAR(1700),
    comment VARCHAR(1700),

    -- Constraints & Foreign Keys
    CONSTRAINT DUPE_FILTER_enum_attribute_acronym UNIQUE(acronym),

    -- Local history columns
    modified_by   VARCHAR(1700),
    modified_date DATETIME2,
    created_by    VARCHAR(1700),
    created_date  DATETIME2,
)
GO

CREATE LOGIN myapp_login WITH PASSWORD='MyFancyPassword123', DEFAULT_DATABASE=myapp;
go

CREATE USER myapp FOR LOGIN myapp_login WITH DEFAULT_SCHEMA=dbo;
go

ALTER ROLE db_owner ADD MEMBER myapp;
go

docker-compose.yml:

services:
    mssql:
        build: .
        container_name: my-mssql-container
        ports:
            - "1433:1433"
        volumes:
            - ./init.sql:/init.sql
            - ./entrypoint.sh:/entrypoint.sh
        command: /bin/bash ./entrypoint.sh
        networks:
            - mssql-network

networks:
    mssql-network:
        driver: bridge

• sql-server
• docker
• docker-compose

Share Improve this question Follow edited Mar 25 at 4:55 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 24 at 18:04 HibbertHibbert 481010 bronze badges 4

• 1 Get the password hash and then create it with said hashed password? – Thom A Commented Mar 24 at 18:23
• 1 Possible cross site duplicate: Best way to create a login with hashed password? – Thom A Commented Mar 24 at 18:29
• 1 Aside... the SA_PASSWORD environment variable has been deprecated for a number of years already, you should be using MSSQL_SA_PASSWORD as per the mcr.microsoft/mssql/server documentation. – AlwaysLearning Commented Mar 24 at 21:44
• You don't want the password in a container-wide environment variable but you still need to create it as a process-scoped environment variable when launching the /opt/mssql/bin/sqlservr process, e.g,: MSSQL_SA_PASSWORD=StrongPassw0rd /opt/mssql/bin/sqlservr. And the same applies to the application login. I suppose it depends on how you're hosting your Docker containers, e.g.: if hosted in AWS then you could retrieve the password from AWS Secrets Manager. – AlwaysLearning Commented Mar 24 at 21:47

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

SQLCMD can inject values from environment variables at runtime. eg:

SET tablename=Person.Person
SET col1=FirstName
SET col2=LastName
SET title=Ms.
sqlcmd -d AdventureWorks2022
1> SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
2> FROM $(tablename)
3> WHERE Title ='$(title)'
4> GO

https://learn.microsoft/en-us/sql/tools/sqlcmd/sqlcmd-use-scripting-variables?view=sql-server-ver16#c-use-command-prompt-environment-variables-within-sqlcmd

For Kubernetes, you can use Kubernetes Secrets

https://kubernetes.io/docs/concepts/configuration/secret/

For Docker Swarm, they also support managing secrets

https://docs.docker/engine/swarm/secrets/

As mentioned in the answer from David Browne, you can then pass the env variable to sqlcmd,

https://learn.microsoft/en-us/sql/tools/sqlcmd/sqlcmd-use-scripting-variables?view=sql-server-ver16#c-use-command-prompt-environment-variables-within-sqlcmd