Connect to MS SQL Server Database in Visual Studio Code

To manage databases on Microsoft SQL Server, you can use the lightweight, cross-platform Visual Studio Code editor (VS Code) instead of SQL Server Management Studio (SSMS). Visual Studio Code with the mssql extension allows administrators and developers to manage databases, tables, and stored procedures, as well as run Transact-SQL (T-SQL) queries against on-premises MS SQL Server and Azure SQL databases.

If you already have VSCode installed on your computer, all you need to do is download and install the mssql extension (https://marketplace.visualstudio.com/items?itemName=ms-mssql.mssql):

1. Go to Extension (Ctrl+Shift+X) and search for mssql;
2. Install mssql for Visual Studio Code;
   
3. After installation, the SQL Server extension icon will appear in the left panel of the VS Code. Click it (or press Ctrl+Alt+D).
   

Now let’s try connecting remotely from VS Code to a host on which MS SQL Server 2019 is installed:

1. Click Add Connection and specify the name of the server and the MS SQL instance (hostname\instance_name);
   The Default instance of SQL Server (MSSQLSERVER) listens on a static TCP port 1433. Specify the SQL Server name and connection port: hostname:1430
2. Select the type of authentication (SQL Login, Integrated, or Azure Active Directory).
   An error may occur when connecting with an MSSQL account:

   mssql: Error 18452: Login failed.
   The login is from an untrusted domain and cannot be used with Integrated authentication.

   
   If so, check that SQL Server and Windows Authentication mode is enabled in the Security tab of the MSSQL settings.

   

3. Enter your username and password. VSCode will prompt you to save the connection credentials;
4. Once connected, you will see a list of databases and other objects. You can expand any database and see a list of tables, views, procedures, etc;
   
5. You can now run various T-SQL queries from the VS Code Editor window. Click the corresponding button in the interface or use the CTRL+SHIFT+E shortcut to run the SQL query. The query results will appear in the next window;
   
6. You can also export a table with the SQL query results directly from VS Code into a CSV, Excel, or JSON file.

VS Code supports all the IntelliSense features for T-SQL: hints, command autocomplete, color schemes for different objects, etc. Visual Studio Code also supports intelligent SQL code snippets for the quick generation of typical SQL queries.

The MSSQL connection settings in VSCode are stored in a JSON file. To view its contents, go to File-> Preferences -> Settings -> Extensions -> MSSQL configuration -> Edit in settings.json. The file contains your MSSQL connection details.

You can also use VSCode instead of PowerShell ISE as your PowerShell script editor. There is also a plugin for VSCode that allows you to remotely connect to a host via SSH.