How to use SQL Server Management Studio with a database container

[clevesque]

Hi all!

Here’s a quick post to show you how to use WorkflowGen’s database container with SQL Server Management Studio. Before you start, make sure to have installed Docker and Docker Compose and SQL Server Management Studio (SSMS).

The first thing you need to do is to create a volume that will contain your WorkflowGen license file:

docker volume create licenses
Copy-Item C:\Path\To\Your\WorkflowGen.lic $(docker volume inspect --format "{{.Mountpoint}}" licenses)

Then, you need to create a Docker Compose file that will set up a WorkflowGen environment in one command. Here’s an example that does that. Save this file as docker-compose.yml.

version: '3.7'
services:
  workflowgen:
    image: advantys/workflowgen:7.16.3-win-ltsc2019
    restart: always
    container_name: workflowgen
    environment:
      WFGEN_DATABASE_CONNECTION_STRING: 'Data Source=database,1433;Network Library=DBMSSOCN;Initial Catalog=WFGEN;User ID=WFGEN_USER;Password=strong(!)Pass;'
      WFGEN_APP_SETTING_ApplicationUrl: 'http://localhost:8080/wfgen'
      WFGEN_APP_SETTING_ApplicationSerialNumber: '<YOUR_WFGEN_LICENSE_NUMBER>'
    ports:
      - '8080:80'
    volumes:
      - 'licenses:C:\wfgen\licenses'
    depends_on:
      - database
  database:
    image: advantys/workflowgen-sql-express:7.16.3-win-ltsc2019
    container_name: workflowgen_database
    environment:
      SA_PASSWORD: 'strong(!)Pass'
      WFGEN_DATABASE_USER_PASSWORD: 'strong(!)Pass'
      WFGEN_ADMIN_PASSWORD: 'strong(!)Pass'
    ports:
      - '8433:1433'
volumes:
  licenses:
    external: true

Notes:

• Replace <YOUR_WFGEN_LICENSE_NUMBER> with your own WorkflowGen license number.

• The host’s port 8433 must be available for this example because it will be mapped to the container port 1433, which is the SQL Server port.

Now, you can bring up your containers by executing the following command:

docker-compose -f docker-compose.yml up

To test if your setup works, open your favorite browser, go to http://localhost:8080/wfgen, and log in to WorkflowGen using the wfgen_admin account and password that you specified for the WFGEN_ADMIN_PASSWORD environment variable. In this example, the value is strong(!)Pass.

You’re now ready to use SQL Server Management Studio with your WorkflowGen environment in containers. Open SSMS and enter the following connection information:

• Server type: Database Engine
• Server name: localhost,8433
• Authentication: SQL Server Authentication
  • Login: sa
  • Password: strong(!)Pass

Note: You also can use the WFGEN_USER SQL Server user account to connect to the database. Use the WFGEN_DATABASE_USER_PASSWORD value for the password.

Here’s what it looks like in SSMS:

You can now fully enjoy the SSMS tools with a database that’s inside a container!