Connecting to the SQL Server with VS Code

A setup guide

Learn how to connect Visual Studio Code to the COMM3220 McIntire SQL Server and create .ipynb notebooks that mix text and runnable SQL queries.

Connection information you will need

  • If off-grounds you need a VPN. Otherwise, turn it off.
  • Profile name: leave empty
  •  Connection group: <Default>
  • Input type: Parameters
  • Server name: f-sg6m-s4.comm.virginia.edu
  • Trust server certificate: yes
  • Authentication type: SQL Login (not 'Windows Authentication')
  • UserID: your UVA userID (e.g., 'tse2z')
  • Initial password: sent to you in MS Teams. Change it as soon as you can — see Step 6.
  • DB: SmallbankDB

If you are working in a McIntire lab, skip to Step 3 (bonus!)

Step 1 — Install Visual Studio Code

Go to code.visualstudio.com/download. Download the installer and run it . Accept the defaults for now. No need to sign-in into GitHub.

Step 2 — Install the SQL Server (mssql) extension

The extension allows VS Code to connect to a SQL Server and to create Jupyter-style SQL Notebooks that let you write text and run SQL queries in the same document.

  • Open VS Code and click the Extensions icon  in the left sidebar. It looks like four little squares, with one square tilted.
  • Search for SQL Server (mssql) by Microsoft, and click Install.
  • Once installed, a new icon appears in the bar on the very left edge of the window. It will be called 'SQL Server' or something similar.

Step 3 — Connect to the server

  • Click the SQL Server icon to open the Connections view.
  • Click Add Connection.
  • Enter the values from the Connection information listed on top of this page: Server, Database, etc.
  • Click Connect. A green/connected indicator next to the connection (top left of your screen) confirms it worked.

Step 4 — Create your first SQL notebook

  • On the top menu, go to View > Command Palette then type "New SQL Notebook," and press Enter.
    Alternatively, you can also right-click your database icon under Connections and choose "New SQL Notebook".
  • If you're prompted to pick a kernel, choose MSSQL.
  • If you're prompted to pick a connection or DB, choose the one you used in Step 3.
  • To save your work, on the top menu, File > Save as... : "MP[miniproject number] [Yourlastname].ipynb"
    for example: "MP1 Dewitt.ipynb".

Step 5 — Add text and SQL cells, and run queries

A SQL Notebook contains two kinds of 'cells': text cells for notes and explanations, and SQL cells for runnable queries. Mix them freely to build a live, self-documenting writeup.

To Add a text ('markdown') cell

  • Click +Markdown in the notebook toolbar.
  • Type your notes using standard Markdown text: for example, a line starting with # for a heading, or **word** for bold. Go here if you want to know more about the markdown syntax.
  • Click the check symbol to render it as formatted text; click back into it any time to edit.

To Add and run a SQL cell

  • Click +Code in the notebook toolbar.
  • Type a SQL query, for example:
SELECT TOP 10 * FROM CUSTOMER;
  • Run the query by clicking the little triangle just to the LEFT of the cell. 
  • Results appear directly below the cell in a data grid you can sort, filter, and copy.

Step 6 — Change your temporary password (recommended, do once)

The password I sent over Teams was shared through a group channel, so change it asap. Connecto to the DB with your old password and then run:

ALTER LOGIN [yourUVAUserID] WITH PASSWORD = 'YourNewStrongPassword'
    OLD_PASSWORD = 'TempPasswordfromTeams';

Getting help

Symptom Likely cause Fix
Connection times out / can't reach server Not on the UVA network Connect to the UVA VPN (Step 1) if you're off Grounds; confirm the VPN shows is connected.
Login failed for user 'YOUR_UVA_ID' Wrong username/password, or still using the old temporary password after changing it Double-check your UVA computing ID and password. Contact Dr. G. for a pwd reset.
Certificate / trust error when connecting The server's certificate isn't automatically trusted by VS Code In the connection's Advanced settings, enable "Trust Server Certificate," then reconnect.
"New Notebook" command is missing The mssql extension is out of date — SQL Notebooks need a recent version Go to Extensions, find SQL Server (mssql), and click Update if available.
Kernel picker doesn't list "MSSQL" The extension is still loading, or VS Code needs a refresh Press Ctrl+Shift+P, run "Developer: Reload Window," and try again.
A SQL cell won't run / no active connection The notebook isn't linked to a saved connection Click the connection indicator at the top of the notebook and choose the connection you saved in Step 4.

If you're stuck after trying the troubleshooting table above, reach out to the help desk (in person or helpdesk@comm.virginia.edu) with the exact error message (copy/paste it, don't paraphrase) and whether you're on the UVA VPN or on Grounds.