Well this is a cautionary tale.
All my settings were correct, the missing piece was publishing from Visual Studio. When I set up the publish settings I either checked "Use this connection string art runtime" or left it checked and this was holding my original connection string before I add the SQL server user and saving it in appsettings.production in the published files which is not a part of my application.
With that being said, and having now noticed this I can now finally get on with my project as it can now connect to SQL.
I don't mind putting this out there if it helps someone else in the future!