Ifejeremiah/Connecting to MSSQL server.md

## Connecting to MSSQL server.md

      
    Raw
  

              Connecting to MSSQL server.md
            
          
    Connecting MSSQL server to Spring-Boot application

SOLVING TCP/IP CONNECTION ERROR

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: “Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.”.  

The environments used as at writing:

$ java --version

openjdk 17.0.4.1 2022-08-12
OpenJDK Runtime Environment Temurin-17.0.4.1+1 (build 17.0.4.1+1) 
OpenJDK 64-Bit Server VM Temurin-17.0.4.1+1 (build 17.0.4.1+1, mixed mode, sharing)    


MSSQL Server Management Studio

SQL Server Management Studio						15.0.18424.0
SQL Server Management Objects (SMO)						16.100.47021.0+7eef34a564af48c5b0cf0d617a65fd77f06c3eb1   
Microsoft Analysis Services Client Tools						15.0.19750.0 
Microsoft Data Access Components (MDAC)						10.0.17763.1
Microsoft MSXML						3.0 6.0
Microsoft .NET Framework						4.0.30319.42000
Operating System						10.0.17763 


IntelliJ IDEA 2022.2.3 (Community Edition)

Build #IC-222.4345.14, built on October 5, 2022
Runtime version: 17.0.4.1+7-b469.62 amd64
VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o.
Windows 10 10.0
GC: G1 Young Generation, G1 Old Generation
Memory: 1000M
Cores: 8
Registry:
    scala.erase.compiler.process.jdk.once=false

Non-Bundled Plugins:
    com.markskelton.one-dark-theme (5.7.0)
    com.haulmont.jpab (2022.4.5-222)
    org.intellij.scala (2022.2.16)

Kotlin: 222-1.7.10-release-334-IJ4345.14

Maven Dependency

<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
  <version>11.2.0.jre11</version>
</dependency>

Configuring Maven application.properties


spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=[database name];encrypt=true;trustServerCertificate=true;integratedSecurity=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


spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.SQLServer2012Dialect
spring.jpa.hibernate.ddl-auto=update


Set integratedSecurity=true for spring.datasource.url if using - Windows Authentication for MSSQL Server. Then you wouldn't need [username] and [password]


Else integratedSecurity=false


Then replace:

[database name] with your database name eg. HRDB
[username] with your login username.
[password] with your login password


spring.jpa.show-sql=true is optional.


Setting TCP PORT


From the Start menu, Open SQL Server 2019 Configuration Manager


Click Protocols for SQLEXPRESS under SQL Server Network Configuration on the left pane.


On the right pane, right-click TCP/IP, and select Properties.


In the TCP/IP Properties dialog box that appears, click the IP Addresses tab.


Scroll down to locate the IPALL node. Remove any value, if present for TCP Dynamic Ports and specify 1433 for TCP Port.


Click OK


Again right-click TCP/IP on the right pane, and select Enable.


In the SQL Server Services node, right-click SQL Server (SQLEXPRESS), and select Restart.


Connecting via MICROSOFT SQL JDBC server

Download the .ZIP:


Microsoft JDBC Driver for SQL Server


Unzip and find mssql-jdbc_auth-11.2.0.x64.dll.


Would be in your downloads file as this:
\Downloads\sqljdbc_11.2.0.0_enu\sqljdbc_11.2\enu\auth\x64\mssql-jdbc_auth-11.2.0.x64.dll 


Open your java directory path, and move the mssql-jdbc_auth-11.2.0.x64.dll file to the Java bin folder.

Java directory path could be as this:
C:\Program Files\Java\jdk-19\bin

Finally, restart your jdk or application.
Contributing

Contributions and reviews are always welcome!
Authors


@Phakocy
@Ifejeremiah