Skip to main content

Notifications

Announcements

No record found.

Community site session details

Community site session details

Session Id :

How to connect to your Business Central Docker sandbox database with SQL Server Management Studio or Visual Studio Code

Kennie Nybo Pontoppidan Profile Picture Kennie Nybo Pontopp...

Getting started with local container sandboxes for Business Central is very easy. Just download and install Docker locally (remember to choose Windows containers), install the NavContainerHelper Powershell module

Install-module navcontainerhelper -force

and run

New-NavContainer -accept_eula -imageName microsoft/bcsandbox:us -containerName <containername> -auth NavUserPassword -updateHosts -usebestcontaineros

(you will be prompted for the username/password that you want to use for the sandbox).

 

Now you can connect to the sandbox from Visual Studio Code and start developing in AL. But what if you want to peek into the database inside the container? Well, this is not that difficult if you know a few SQL Server and Docker tricks…



The commandlet New-NavContainer will place two shortcuts on your desktop to help connect to the container: <container name> Command Prompt and <container name> Powershell Prompt. If you still have them, skip the step below on how to connect and go to the paragraph about sqlcmd. If not, then you need to find the container ID for your sandbox. In an elevated Powershell prompt, type

docker ps

This shows information about all running docker containers:

CONTAINER ID       IMAGE                                 COMMAND                 CREATED            STATUS               PORTS                                               NAMES

8e0967a5b798       microsoft/dynamics-nav:generic-1803   "powershell -Command…"   7 days ago         Up 7 days (healthy)   80/tcp, 443/tcp, 1433/tcp, 7045-7049/tcp, 8080/tcp   <containername>

In this example, the container ID is 8e0967a5b798.

 

From the same Powershell prompt type

docker exec -it 8e0967a5b798 powershell

This opens a new Powershell prompt where you are now connected to the container.



In the new prompt type

sqlcmd

to start the SQL Server commandline utility. Now you are connected to the default SQL Server instance in your sandbox. You can test that this is indeed the case by querying for the databases inside the instance:

1> select name from sys.databases

2> go

name                                                                                                                  

--------------------------

master                                                                                                                

tempdb                                                                                                                

model                                                                                                                  

msdb                                                                                                                  

CRONUS                                                                                                                

(5 rows affected)

Notice that the command prompt tool sqlcmd requires you to end all T-SQL commands with the GO keyword.

 

Running New-NavContainer with the -auth NavUserPassword switch actually creates a SQL Server user with the same username and password as you specify for Business Central. But you can also use T-SQL to create a user that can be used to connect to the database from your host OS:

 

1> CREATE LOGIN <loginname> WITH PASSWORD = 'WRITE PASSWORD HERE';

2> GO

1> CREATE USER <loginname> FOR LOGIN <loginname>;

2> GO

 

Finally, you need to grant the new user read access to the Business Central database:

1> use CRONUS

2> GO


Changed database context to 'CRONUS'.

2> ALTER ROLE db_datareader ADD MEMBER <loginname>

3> GO

And that should be it. Let’s test if it works...

 

If you ran New-NavContainer with the -updatehosts switch, the Docker will add a hosts file entry mapping the host name to the IP address on your OS. You can check for yourself by inspecting your hosts file

C:\>more c:\Windows\System32\drivers\etc\hosts

192.168.1.240 host.docker.internal

192.168.1.240 gateway.docker.internal

172.25.63.140 kennietest

(in my example, the container is called kennietest).
 

Or you can ping your container:

C:\>ping kennietest

 
Pinging kennietest [172.25.63.140] with 32 bytes of data:

Reply from 172.25.63.140: bytes=32 time<1ms TTL=128

If you didn’t use the -updatehosts switch, then use the docker inspect command to get the IP address:

docker inspect <container id>


You can filter away everything else that the IP address with this filter:

docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' <container id>


Open SQL Server Management Studio and connect, either with the host name or with the IP address of the container:

ssms-_2D00_-1.png

and check if you can access the Business Central tables

ssms-_2D00_-2.png

You can also use Visual Studio Code to connect to the database using the mssql extension (see link below on how to set this up).

vscode.png

 

That’s all folks. Stay tuned for a new Holiday count down blog post tomorrow...

Get a list of all posts in the Holiday count down series here:
https://community.dynamics.com/business/b/financials/archive/2018/11/28/counting-down-to-the-holidays-with-daily-blogs

References:

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/grant-a-permission-to-a-principal?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-develop-use-vscode?view=sql-server-2017

Comments

*This post is locked for comments