TLS 1.2 enabled issue with sql server 2012 and 2014

When you try to install Microsoft SQL Server 2012 or SQL Server 2014 on a server that has Transport Layer Security (TLS) version 1.2 enabled, you may encounter the following issues:

TSL is new and advanced version of SSL and mostly added to new OS systems which causing compatibility issue with sql server connection during and after installation. Versions of TLS — v1.1 and v1.2 are significantly more secure and fix many vulnerabilities present in SSL v3.0 and TLS v1.0.

• If the version of SQL Server that you're trying to install doesn't contain the fix to enable TLS 1.2 support, you receive the following error message:
  Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
• If the version of SQL Server that you're trying to install does contain the fix to enable TLS 1.2 support, you receive the following error message:
  A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)
  In both of these situations, the installation fails.
   

Cause  :

This issue occurs because SQL Server Setup uses SqlClient for ADO.NET from the .NET Framework 2.0. By default, the .NET Framework 2.0 works with TLS 1.0.

To resolve this issue, install the hotfix rollup for the .NET Framework, and then restart the server. Based on the operating system and SQL Server version that you're running: eg : Hotfix rollup 3106993 for the .NET Framework 2.0 SP2 for Windows 2012 R2 and Windows 8.1.

 

To work around this issue, follow these steps:

Run the SQL Server 2012 or SQL Server 2014 Setup program, and update the SQL Server version to a build that supports TLS 1.2. (For more information about the updates that add support for TLS 1.2, see KB 3052404.)

Use slipstream method while using workaround method with Cumulative updated and sql full installation simultaneously, below are the sql updates that has compatibility with TLS 1.2

Cumulative Update 12 for SQL Server 2014 
Cumulative Update 5 for SQL Server 2014 SP1 
Cumulative Update 10 for SQL Server 2012 SP2
Cumulative Update 1 for SQL Server 2012 SP3

 

After this SQL server will start but won’t be able to connect locally using SSMS as client requires TLS1.0 and 1.1 should be enabled in below registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0]  

 If we don’t want to enable TLS 1.0 and TLS 1.1 for security reason the other option would be install .Net 4.6+ which can be found here and everything will work fine.

https://www.microsoft.com/en-us/download/details.aspx?id=48130

 

Slipstream service pack, patch or cumulative updates with sql installation for 2012 and 2014

Slipstream service pack, patch or cumulative updates with sql installation for 2012 and 2014:

There are two scenario when we want to install SQL Server from scracth :

1. There are service pack or updates which is required to add after installation.
2. Or sql installation fails with some error and to resolve the issue Microsoft has released some cumulative updates.

In both the scenario we can install sql with all updates simultaneously using slipstream method, because you don’t want to first install the RTM version of the product and then manually patch the instance with the Service Packs, Cumulative Updates or Hotfixes that you think are needed

The method would be:

1. Download SQL Server 2012/2014 media.
2. Download all the needed SPs, CUs and Hotfixes that you want to “embed” and put all of the .exe files in one directory. (make sure they are executable files not in zipped format(unzip if not done already))
3. Launch the setup.exe from CMD by issuing the command:

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource=”path_to_the_directroy_where_the_hotfixes_are”

 

So let’s assume SQL installation setup file is in D:\SQL2014\ folder and hotfixex could be in same directory

D:\ Fixes\      Or  keep updates in same software path to ease copy method in next machine D:\SQL2014\updates folder

We can go to command prompt change current path to software path (D:\SQL2014\) and run below command as per update path in updatesource parameter:

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource="D:\Fixes"

Or

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource=".\Updates"

No more it is required to manually start every single Service Pack then Cumulative update and Security Hotfix you need! Now you just put all of those in a folder and “point” SQL Server’s setup to that folder. 

Find service account and status of SQL Service using TSQL

Since SQL server 2008R2 we can use DMV (sys.dm_server_services) to get service account  information, It gives us information about the current state of the services related to SQL Server that are currently installed.

SELECT  servicename,

        startup_type_desc,

        status_desc,

        last_startup_time,

        service_account,

        is_clustered,

        cluster_nodename,

        filename,

        startup_type,

        status,

        process_id

FROM    sys.dm_server_services AS ;

 

In SQL 2008 R1 and 2005 there is way to use registry read command to get same information.

 

DECLARE @sa NVARCHAR(128);

EXEC master.dbo.xp_regread

    'HKEY_LOCAL_MACHINE',

    'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',

    'ObjectName',

    @sa OUTPUT;

SELECT @sa;