Last modified by S V on 2023/03/07 12:18
Show last authors
1 {{aagon.floatingbox/}}
2
3 {{aagon.priorisierung}}
4 160
5 {{/aagon.priorisierung}}
6
7 = **Establishing an encrypted connection between ACMP Server and SQL Server** =
8
9 It is possible to establish an encrypted connection between the ACMP Server and the SQL Server. There are two ways to use transport encryption:
10
11 * Use Transport Layer Security to encrypt the data.
12 * Set up an IPSec tunnel in which the communication data is routed.
13
14 == Transport Layer Security ==
15
16 An SQL Server can use Transport Layer Security (TLS) to encrypt data as it travels.
17
18 === Create a certificate ===
19
20 The easiest way to create a certificate is to use the following PowerShell script. However, you must have administrator rights to do this:
21
22 **Powershell script to create a certificate**
23 {{code language="CSV"}}$dnsname = ([System.Net.Dns]::GetHostByName((hostname)).HostName)
24 #Create SSL Certificate (replace with PKI function)
25 New-SelfSignedCertificate -CertStoreLocation Cert:\LocalMachine\My -subject
26 $Subject  -DnsName $dnsname -FriendlyName SQLServer -NotAfter (Get-Date).AddMonths(24) -KeySpec KeyExchange{{/code}}
27
28 If you take a different route and use certificates from MakeCert.exe, OpenSSL or another CA, you must ensure that the certificate has certain [[properties>>https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-2017]]:
29
30 * The FQDN of the SQL Server must be included in the Subject //CN// and in the //SAN// extension.
31 * //Digital Signature// and //Key Encryption// must be listed in the //Key Usage// extension.
32 * //Server Authentication// (1.3.6.1.5.5.7.3.1) must be listed in the //Enhanced Key Usage// extension.
33
34
35 The certificate and private key must be stored in the Windows Certificate Store //Personal// for Local Machine. In addition, the user running SQL Server must have read access to the private key.
36
37
38
39
40
41 **Granting read permission to the SQL Server service**
42
43 Typically, the SQL Server service runs in a [[virtual account>>https://www.sqlservercentral.com/blogs/sql-server-2012-and-virtual-service-accounts]] rather than as a local service account. The virtual account has limited rights.
44
45 **Specifying the SQL Server service account**
46
47 To find the SQL Server service account, you must first open the SQL Server Configuration Manager. Then select and open the SQL Server services. Open the SQL Server instance properties. The account name used is stored in the //Log-on// tab.
48
49 {{aagon.infobox}}
50 The default account name is //NT Service\MSSQLSERVER or NT Service\MSSQL$<Instance Name>//.
51 {{/aagon.infobox}}
52
53 {{figure}}
54 (% style="text-align:center" %)
55 [[image:Account des SQL Server Dienst bestimmen.png||height="624" width="826"]]
56
57 {{figureCaption}}
58 Determine the account of the SQL Server service
59 {{/figureCaption}}
60 {{/figure}}
61
62 **Enabling Read Permissions**
63
64 If you want to establish an encrypted connection between the ACMP Server and the SQL Server, you need to enable read rights for the SQL Server service account.
65 To do this, first open the Cert Manager for the local computer (e.g. open mmc.exe and add the snap-in) and then select the //Personal// > //Certificates// //directory//. Locate the certificate for which you want to enable read rights and right-click on it. In the menu that opens, select //All Tasks// > //Manage Private Keys...//.
66
67 {{figure}}
68 (% style="text-align:center" %)
69 [[image:Leserechte gewähren.png]]
70
71 {{figureCaption}}
72 Open Cert Manager for the Local Computer
73 {{/figureCaption}}
74 {{/figure}}
75
76 Then add the SQL Server account. In the following picture, the account is //NT Service\MSSQL$SQLEXPRESS//. Once you have added the account, you must give it the appropriate permissions. Enable read-only and confirm with //OK//.
77
78 {{figure}}
79 (% style="text-align:center" %)
80 [[image:Leserechte dem Nutzer gewähren.png]]
81
82 {{figureCaption}}
83 Add read rights to the account
84 {{/figureCaption}}
85 {{/figure}}
86
87 {{aagon.infobox}}
88 When adding the SQL Server account, make sure that the local machine is selected as the location.
89 {{/aagon.infobox}}
90
91 === Assigning the certificate to SQL Server ===
92
93 Once you make changes to the settings, you will need to restart the SQL service for them to take effect.
94 A [[Stack Overflow answer>>https://stackoverflow.com/questions/36817627/ssl-certificate-missing-from-dropdown-in-sql-server-configuration-manager/36823345#36823345]] not only details how the settings dialogue determines whether a certificate can be selected, but also explains the alternative way via the registry. It also explains that wildcard certificates are generally supported, but in some cases cannot be selected through the interface. If you choose the registry method anyway, the association will still work.
95
96 **Manually**
97 \\To assign a certificate to SQL Server manually, you need to follow a few steps. First, open the SQL Server Configuration Manager and expand the SQL Server Network Configuration menu item. There you will find //Protocols for SQLEXPRESS//, which you must open by right-clicking and selecting //Properties//.
98
99 {{figure}}
100 (% style="text-align:center" %)
101 [[image:SQL Server Configuration Manager.png]]
102
103 {{figureCaption}}
104 SQL Server Network Configuration
105 {{/figureCaption}}
106 {{/figure}}
107
108 In the window that opens, navigate to the //Certificate// tab and select the appropriate certificate. In the figure below it is the //SQLServer// certificate. Then go back to the first tab //Flags// and select the option //Force Encryption// by setting it to //Yes//.
109
110 {{figure}}
111 (% style="text-align:center" %)
112 [[image:Protocols for SQLEXPRESS Properties.png]]
113
114 {{figureCaption}}
115 Protocoll for SQLEXPRESS Properties
116 {{/figureCaption}}
117 {{/figure}}
118
119 **Via the registry**
120
121 The certificate can also be set via the registry (as part of a script). To do this, create a string called Certificate at //HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.%Instance%\MSSQLServer\SuperSocketNetLib// (replace //%Instance%// with the instance name). The value (in lower case) must be the thumbprint (usually SHA1) of the certificate to be used. Next, create a //DWord// called //ForceEncryption// and set it to //1//.
122
123 === Distribute the certificate ===
124
125 In order for the remote peer to accept the offered certificate, it must be recognised as trustworthy. To do this, it must be imported into the Windows Certificate Store. Note that this must be done for each machine that will access the SQL Server via the secure connection, even if this is done locally.
126
127 === **Testing with SSMS** ===
128
129 You can use SQL Server Management Studio to test that the secure connection is indeed successful. To do this, start the application, navigate to the //Connection Properties// tab under Options and select the //Encrypt connection// check box.
130 If you select the //Trust server certificate// checkbox, any certificate will be considered valid. This is not recommended for production use!
131
132 {{figure}}
133 (% style="text-align:center" %)
134 [[image:Test mit SSMS.PNG]]
135
136 {{figureCaption}}
137 Connection test via the SQL Server Management Studio
138 {{/figureCaption}}
139 {{/figure}}
140
141 {{aagon.infobox}}
142 The certificate will only be successfully validated if the connection is made using the name stored in the certificate - the FQDN. In the image below, this is the server name entry, which has been partially obscured for privacy reasons.
143 {{/aagon.infobox}}
144
145 {{figure}}
146 (% style="text-align:center" %)
147 [[image:SSMS Login.PNG]]
148
149 {{figureCaption}}
150 Connect to Server
151 {{/figureCaption}}
152 {{/figure}}
153
154 == A**djusting the ACMP server connection string** ==
155
156 You can also customise the connection settings in SQL Server Management Studio using the ACMP server connection string:
157 \\{{code language="CSV"}}Provider=SQLNCLI11.1;Password=MeinGeheimesPW;Persist Security Info=True;User ID=ACMPDBUser;Initial Catalog=ACMP;
158 Data Source=ServerNamenEingeben.aagon.local\SQLEXPRESS;Use Encryption for Data=True;MARS Connection=False;DataTypeCompatibility=80;Trust Server Certificate=False{{/code}}
159
160 This requires the //**Use Encryption for Data**// property to be set to //**True**//.
161
162 {{aagon.infobox}}
163 The connection must also be made using the FQDN, otherwise the certificate will not be considered valid.
164 {{/aagon.infobox}}
165
166 If the //Trust Server Certificate// property is also set to True, any certificate will be considered valid.
167
168 {{aagon.infobox}}
169 Read here how to customise the connection string and attributes.
170 {{/aagon.infobox}}
171
172 == IPSec ==
173
174 Alternatively, if you want to use an IPSec tunnel to establish an encrypted connection between the ACMP Server and the SQL Server, read the procedure here.
175
176 === Next recommended actions ===
177
178 * [[Customise connection string>>https://stackoverflow.com/questions/36817627/ssl-certificate-missing-from-dropdown-in-sql-server-configuration-manager/36823345#36823345]]

Navigation

© Aagon GmbH 2024
Besuchen Sie unsere neue Aagon-Community