Getting started with Spring boot and MS SQL Server

MS SQL Server one of the several RDBMS servers available in the marker. In this post we will see how to install and setup SQL Server and then later how to connected to it from a Spring boot application.

Installing MS SQL Server

MS provides a free developer version for local development. Download the installer from here and install the server.

Install Microsoft SQL Server Management studio (SSMS)

Download and install SQL Server management studio

Preparing the database

Run the SQL Server Configuration manager and perform following steps

1. enable TCP/IP Protocol
2. restart server

enable TCP/IP Protocol

restart the SQL Server service

right click on the “SQL Server” and select restart.

Run the SQL Server management studio and perform the following steps

1. enable contained databases
2. enable SQL Server Authentication
3. restart the server
4. Create a new contained database
5. Create new schema
6. Create new SQL User

Enable contained databases

Right click on the server and select properties, in properties -> Advanced you will have option to enable/disable contained database

Enable SQL Server Authentication

In server properties, select security and enable both SQL Server and Windows authentication mode

Create a new contained database

Right click on Databases and select “New Database”

Give a name to the new database and click on “Options” in new database dialog box. Set the containment type to “Partial”.

Create new schema

Create new SQL User

Expand security under newly created database and right click on “Users” and select “New User”

give credentials and also provide the default schema name

Click on “Owned Schemas” and select schemas

provide appropriate memberships

Preparing Spring boot project

Create a spring boot project if you don’t already have one using spring project creator.

Add MS SQL Server JDBC Driver depedency

add the following dependency to your gradle dependencies ( use a version that matches with your project JDK)

implementation group: 'com.microsoft.sqlserver', name: 'mssql-jdbc', version: '12.4.2.jre11'

JDBC Connection String

Add connection settings to application properties file (you need to change database name, username and password)

spring.datasource.url=jdbc:sqlserver://localhost;databaseName=<database-name>;encrypt=true;trustServerCertificate=true
spring.datasource.username=<username>
spring.datasource.password=<password>
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.show-sql=true
spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect

Now your spring project will be able to connect to the SQL Server