On Saturday I spent time working on a personal project where I initially had decided to go with SQLite but the limitations of it and the fact that I host this app on Azure made me switch to SQL Server. I had initially opted for SQLite as my plan was to deploy the application on Azure App Service and connect the App Service with a File share, unfortunately due to limitations read / write support was not possible. Read more about it here

I'll make a short pause here and clarify that by limitations I mean the process I would have to follow on my CI / CD pipelines to backup the .db file before every deployment and restore it. Which although automated and can work I find exhausting and don't want to spend time on this when I could spend 3£ and use a serverless database on Azure.

Back to the issue, with SQL Server on the stack I downloaded the 2017 SQL Server image from Microsoft Docker Hub Repo, fired up the container and verified that I could connect with my user.

Next step was to run the database update command to apply the migrations. Instantly I was greeted with the following message. The issue was well documented on this article on Stack Overflow.

Using the Trusted_Connection property on the connection string caused the exception, so setting it to false fixed the issue. As per the documentation

If User ID and Password are specified and Integrated Security is set to true, the User ID and Password will be ignored and Integrated Security will be used. source https://bit.ly/2WdFvnp
dotnet ef database update
https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?redirectedfrom=MSDN&view=dotnet-plat-ext-3.1#System_Data_SqlClient_SqlConnection_ConnectionString
Trusted_Connection set to false

Blog post image credits to: http://ericsmasal.com/2018/06/22/sql-server-2016-on-docker-for-development/

Thanks for reading