How to enable TLS 1.2 in TOMCAT Server?

TLS (Transport Layer Security) is a widely adopted security protocol designed to facilitate privacy and data security for communications over the Internet. TLS is just an updated, more secure, version of SSL. We still refer to our security certificates as SSL because it is a more commonly used term, but when you are buying SSL from Symantec you are actually buying the most up to date TLS certificates with the option of ECC, RSA or DSA encryption.

A primary use case of TLS is encrypting the communication between web applications and servers, such as web browsers loading a website.

TLS provides you with the ability to encrypt connections between SQL Server and calling client applications.  When a client requests an encrypted connection to a SQL Server configured for TLS, an initial handshake takes place to negotiate the cipher suite from which further communication should take place.  Once agreed, SQL Server then sends its TLS certificate to the client, which the client must then validate and trust against its copy of the Certification Authority (CA) certificate.  Finally, providing the TLS certificate is trusted and it meets certain other requirements, a secure connection is established.

TLS Background:

We were able to secure communication channels since SQL Server 2000 and as the cryptographic protocols have become more and more secure over time, it’s good to see that Microsoft has continued to ensure that the product hasn't been left behind. In January 2016, Microsoft announced support for TLS 1.2 encryption for SQL Server 2008, 2008 R2, 2012 and 2014.  That announcement has since been updated to include support for SQL Server 2016 and 2017.  Previously, Microsoft only supported SSL encryption in SQL Server, however given the series of reported vulnerabilities against SSL, Microsoft now recommends that you move to TLS 1.2 since it is more secure for establishing the connection. 

SQL Server can use Transport Layer Security (TLS) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. The TLS encryption is performed within the protocol layer and is available to all supported SQL Server clients. The level of encryption used by TLS, 40-bit or 128-bit, depends on the version of the Microsoft Windows operating system that is running on the application and database computers.

In System Registry the following changes need to be applied to enable the TLS 1.2

“HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\

SCHANNEL\Protocols\”

TLS 1.0

Client

                        DisabledByDefault - 1

                        Enabled                 - 0          

            Server

                        DisabledByDefault - 1

                        Enabled                 - 0          

TLS 1.1

Client

            DisabledByDefault - 1

            Enabled                 - 0

Server

            DisabledByDefault - 1

            Enabled                 - 0          

TLS 1.2

Client

            DisabledByDefault - 0

            Enabled                 - 1          

Server

            DisabledByDefault - 0

            Enabled                 - 1   

TOMCAT Server.xml

Edit “C:\Program Files (x86)\apache-tomcat-**\conf\Server.xml” file with sslProtocol="TLSv1.2 and sslEnabledProtocols="TLSv1.2" for connecter element as follows:


<connector acceptcount="100" clientauth="false" disableuploadtimeout="true" enablelookups="false" keystorefile="C:\Program Files\Java\jdk1.8.0_51\bin\tomcat.keystore" keystorepass="@****" maxthreads="200" port="8443" protocol="org.apache.coyote.http11.Http11NioProtocol" scheme="https" secure="true" sslenabled="true" sslenabledprotocols="TLSv1.2" sslprotocol="TLSv1.2">

How to enable TLS 1.2 in SQL server?

TLS (Transport Layer Security) is a widely adopted security protocol designed to facilitate privacy and data security for communications over the Internet. TLS is just an updated, more secure, version of SSL. We still refer to our security certificates as SSL because it is a more commonly used term, but when you are buying SSL from Symantec you are actually buying the most up to date TLS certificates with the option of ECC, RSA or DSA encryption.

A primary use case of TLS is encrypting the communication between web applications and servers, such as web browsers loading a website.

TLS provides you with the ability to encrypt connections between SQL Server and calling client applications.  When a client requests an encrypted connection to a SQL Server configured for TLS, an initial handshake takes place to negotiate the cipher suite from which further communication should take place.  Once agreed, SQL Server then sends its TLS certificate to the client, which the client must then validate and trust against its copy of the Certification Authority (CA) certificate.  Finally, providing the TLS certificate is trusted and it meets certain other requirements, a secure connection is established.

TLS Background:

We were able to secure communication channels since SQL Server 2000 and as the cryptographic protocols have become more and more secure over time, it’s good to see that Microsoft has continued to ensure that the product hasn't been left behind. In January 2016, Microsoft announced support for TLS 1.2 encryption for SQL Server 2008, 2008 R2, 2012 and 2014.  That announcement has since been updated to include support for SQL Server 2016 and 2017.  Previously, Microsoft only supported SSL encryption in SQL Server, however given the series of reported vulnerabilities against SSL, Microsoft now recommends that you move to TLS 1.2 since it is more secure for establishing the connection. 

SQL Server can use Transport Layer Security (TLS) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. The TLS encryption is performed within the protocol layer and is available to all supported SQL Server clients. The level of encryption used by TLS, 40-bit or 128-bit, depends on the version of the Microsoft Windows operating system that is running on the application and database computers.

In System Registry the following changes need to be applied to enable the TLS 1.2

“HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\

SCHANNEL\Protocols\”

TLS 1.0

Client

                        DisabledByDefault - 1

                        Enabled                 - 0          

            Server

                        DisabledByDefault - 1

                        Enabled                 - 0          

TLS 1.1

Client

            DisabledByDefault - 1

            Enabled                 - 0

Server

            DisabledByDefault - 1

            Enabled                 - 0          

TLS 1.2

Client

            DisabledByDefault - 0

            Enabled                 - 1          

Server

            DisabledByDefault - 0

            Enabled                 - 1          

References:

https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15

DELETE statement conflicted with the REFERENCE constraint

 -- Create Department table with necessary constraints

 CREATE TABLE [dbo].[Department](

    [DepartmentID] [int] NOT NULL IDENTITY(0,1),

    [DepartmentName] [varchar](50) NOT NULL,

    [IsActive] [bit] NOT NULL CONSTRAINT DF_Department_IsActive DEFAULT (1)

    CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED

    (

            [DepartmentID] ASC

    ) ON [PRIMARY]

    ) ON [PRIMARY]

 -- Create Employee table with necessary constraints

 CREATE TABLE [dbo].[Employee](

    [EmployeeID] int NOT NULL IDENTITY(1,1),

    [DepartmentID] int NOT NULL CONSTRAINT FK_Employee_DepartmentID

                              REFERENCES [Department](DepartmentID),

    [EmployeeName] varchar(50) NOT NULL,

    [Gender] char(1) NOT NULL CONSTRAINT CH_Employee_Gender

                     CHECK ([Gender] in('M','F','O')),

    [BirthDate] date NOT NULL,

    [Salary] decimal(10,2) NULL,

    [Detail] varchar(max) NULL,

    [IsActive] bit NOT NULL CONSTRAINT DF_Employee_IsActive DEFAULT (1),

    CONSTRAINT [UQ_Employee_EmployeeName] UNIQUE(EmployeeName),

    CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED

      (

          [EmployeeID] ASC

      ) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-- Insert into Department table

INSERT INTO [dbo].[Department]

           ([DepartmentName]

           ,[IsActive])

VALUES ('(None)',1)

GO

INSERT INTO [dbo].[Department]

           ([DepartmentName]

           ,[IsActive])

VALUES ('Computer Science',1)

GO

INSERT INTO [dbo].[Department]

           ([DepartmentName]

           ,[IsActive])

VALUES ('Mathematics',1)

GO

INSERT INTO [dbo].[Department]

           ([DepartmentName]

           ,[IsActive])

VALUES ('Physics',1)

-- Insert into Employee table

INSERT INTO [dbo].[Employee]

           ([DepartmentID]

           ,[EmployeeName]

           ,[Gender]

           ,[BirthDate]

           ,[Salary]

           ,[Detail]

           ,[IsActive])

VALUES

           (1

           ,'Govindaraj Kanniappan'

           ,'M'

           ,'01/01/1970'

           ,10000.00

           ,'Govindaraj Kanniappan M.C.A, Mphil (CS)'

           ,1)

GO

INSERT INTO [dbo].[Employee]

           ([DepartmentID]

           ,[EmployeeName]

           ,[Gender]

           ,[BirthDate]

           ,[Salary]

           ,[Detail]

           ,[IsActive])

VALUES

           (2

           ,'Anusha Karayyan'

           ,'M'

           ,'01/01/1975'

           ,90000.00

           ,'Anusha MA'

           ,1)

INSERT INTO [dbo].[Employee]

           ([DepartmentID]

           ,[EmployeeName]

           ,[Gender]

           ,[BirthDate]

           ,[Salary]

           ,[Detail]

           ,[IsActive])

VALUES

           (1

           ,'Krishna Rajan'

           ,'M'

           ,'01/01/1973'

           ,90500.00

           ,'Krishna Rajan MCA'

           ,1)

GO

-- Select from Department

SELECT DepartmentID, DepartmentName FROM Department WITH (NOLOCK)

DepartmentID	DepartmentName
0	(None)
1	Computer Science
2	Mathematics
3	Physics

-- Select from Employee table

SELECT EmployeeID, DepartmentID, EmployeeName FROM Employee WITH (NOLOCK)

EmployeeID	DepartmentID	EmployeeName
1	1	Govindaraj Kanniappan
2	2	Anusha Karayyan
3	1	Krishna Rajan

-- Delete from Department table

DELETE [dbo].[Department] WHERE DepartmentID = 1

Error:

Msg 547, Level 16, State 0, Line 62

The DELETE statement conflicted with the REFERENCE constraint "FK_Employee_DepartmentID". The conflict occurred in database "TestDB", table "dbo.Employee", column 'DepartmentID'.

The statement has been terminated.

SOLUTION: 1

First delete the child table (Employee) rows that point to the row(s) you are trying to delete, then delete the row.

-- Delete from Employee table

DELETE [dbo].[Employee] WHERE DepartmentID = 1

(2 row(s) affected)

-- Delete from Department table

DELETE [dbo].[Department] WHERE DepartmentID = 1

(1 row(s) affected)

-- Select from Department

SELECT DepartmentID, DepartmentName FROM Department WITH (NOLOCK)

DepartmentID	DepartmentName
0	(None)
2	Mathematics
3	Physics

-- Select from Employee table

SELECT EmployeeID, DepartmentID, EmployeeName FROM Employee WITH (NOLOCK)

EmployeeID	DepartmentID	EmployeeName
2	2	Anusha Karayyan

SOLUTION: 2

You can modify the FK to add with “ON DELETE CASCADE”

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Employee_DepartmentID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Employee]'))

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [FK_Employee_DepartmentID]

GO

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_DepartmentID] FOREIGN KEY([DepartmentID])

REFERENCES [dbo].[Department] ([DepartmentID]) ON DELETE CASCADE

GO

-- Delete from Department table

DELETE [dbo].[Department] WHERE DepartmentID = 1

(1 row(s) affected)

-- Select from Department

SELECT DepartmentID, DepartmentName FROM Department WITH (NOLOCK)

DepartmentID	DepartmentName
0	(None)
2	Mathematics
3	Physics

-- Select from Employee table

SELECT EmployeeID, DepartmentID, EmployeeName FROM Employee WITH (NOLOCK)

EmployeeID	DepartmentID	EmployeeName
2	2	Anusha Karayyan