Changes for page Verbindung vom acmp Server zum SQL Server einrichten

Summary

• Page properties (3 modified, 0 added, 0 removed)

Details

Page properties

Title

...	...	@@ -1,1 +1,1 @@
1		-Connecting acmp Server to SQL Server
	1	+Connecting ACMP Server to SQL Server

Author

...	...	@@ -1,1 +1,1 @@
1		-XWiki.SV
	1	+XWiki.jklein

Content

...	...	@@ -4,9 +4,9 @@
4	4	160
5	5	{{/aagon.priorisierung}}
6	6
7		-= **Establishing an encrypted connection between acmp Server and SQL Server** =
	7	+= **Establishing an encrypted connection between ACMP Server and SQL Server** =
8	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:
	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	10
11	11	* Use Transport Layer Security to encrypt the data.
12	12	* Set up an IPSec tunnel in which the communication data is routed.
...	...	@@ -31,9 +31,12 @@
31	31	* //Digital Signature// and //Key Encryption// must be listed in the //Key Usage// extension.
32	32	* //Server Authentication// (1.3.6.1.5.5.7.3.1) must be listed in the //Enhanced Key Usage// extension.
33	33
	34	+
34	34	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.
35	35
	37	+
36	36
	39	+
37	37
38	38	**Granting read permission to the SQL Server service**
39	39
...	...	@@ -48,7 +48,8 @@
48	48	{{/aagon.infobox}}
49	49
50	50	{{figure}}
51		-[[image:Account des SQL Server Dienst bestimmen.png||data-xwiki-image-style-alignment="center" height="624" width="826"]]
	54	+(% style="text-align:center" %)
	55	+[[image:Account des SQL Server Dienst bestimmen.png||height="624" width="826"]]
52	52
53	53	{{figureCaption}}
54	54	Determine the account of the SQL Server service
...	...	@@ -57,11 +57,12 @@
57	57
58	58	**Enabling Read Permissions**
59	59
60		-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.
	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.
61	61	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...//.
62	62
63	63	{{figure}}
64		-[[image:Leserechte gewähren.png||data-xwiki-image-style-alignment="center"]]
	68	+(% style="text-align:center" %)
	69	+[[image:Leserechte gewähren.png]]
65	65
66	66	{{figureCaption}}
67	67	Open Cert Manager for the Local Computer
...	...	@@ -71,7 +71,8 @@
71	71	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//.
72	72
73	73	{{figure}}
74		-[[image:Leserechte dem Nutzer gewähren.png||data-xwiki-image-style-alignment="center"]]
	79	+(% style="text-align:center" %)
	80	+[[image:Leserechte dem Nutzer gewähren.png]]
75	75
76	76	{{figureCaption}}
77	77	Add read rights to the account
...	...	@@ -91,7 +91,8 @@
91	91	\\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//.
92	92
93	93	{{figure}}
94		-[[image:SQL Server Configuration Manager.png||data-xwiki-image-style-alignment="center"]]
	100	+(% style="text-align:center" %)
	101	+[[image:SQL Server Configuration Manager.png]]
95	95
96	96	{{figureCaption}}
97	97	SQL Server Network Configuration
...	...	@@ -101,7 +101,8 @@
101	101	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//.
102	102
103	103	{{figure}}
104		-[[image:Protocols for SQLEXPRESS Properties.png||data-xwiki-image-style-alignment="center"]]
	111	+(% style="text-align:center" %)
	112	+[[image:Protocols for SQLEXPRESS Properties.png]]
105	105
106	106	{{figureCaption}}
107	107	Protocoll for SQLEXPRESS Properties
...	...	@@ -122,7 +122,8 @@
122	122	If you select the //Trust server certificate// checkbox, any certificate will be considered valid. This is not recommended for production use!
123	123
124	124	{{figure}}
125		-[[image:Test mit SSMS.PNG||data-xwiki-image-style-alignment="center"]]
	133	+(% style="text-align:center" %)
	134	+[[image:Test mit SSMS.PNG]]
126	126
127	127	{{figureCaption}}
128	128	Connection test via the SQL Server Management Studio
...	...	@@ -134,7 +134,8 @@
134	134	{{/aagon.infobox}}
135	135
136	136	{{figure}}
137		-[[image:SSMS Login.PNG||data-xwiki-image-style-alignment="center"]]
	146	+(% style="text-align:center" %)
	147	+[[image:SSMS Login.PNG]]
138	138
139	139	{{figureCaption}}
140	140	Connect to Server
...	...	@@ -141,9 +141,9 @@
141	141	{{/figureCaption}}
142	142	{{/figure}}
143	143
144		-== A**djusting the acmp server connection string** ==
	154	+== A**djusting the ACMP server connection string** ==
145	145
146		-You can also customise the connection settings in SQL Server Management Studio using the acmp server connection string:
	156	+You can also customise the connection settings in SQL Server Management Studio using the ACMP server connection string:
147	147	\\{{code language="CSV"}}Provider=SQLNCLI11.1;Password=MeinGeheimesPW;Persist Security Info=True;User ID=ACMPDBUser;Initial Catalog=ACMP;
148	148	Data Source=ServerNamenEingeben.aagon.local\SQLEXPRESS;Use Encryption for Data=True;MARS Connection=False;DataTypeCompatibility=80;Trust Server Certificate=False{{/code}}
149	149
...	...	@@ -161,8 +161,8 @@
161	161
162	162	== IPSec ==
163	163
164		-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>>https://stackoverflow.com/questions/36817627/ssl-certificate-missing-from-dropdown-in-sql-server-configuration-manager/36823345#36823345]].
	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.
165	165
166	166	=== Next recommended actions ===
167	167
168		-* [[Customise connection string>>doc:.Connectionstring anpassen.WebHome]]
	178	+* [[Customise connection string>>https://stackoverflow.com/questions/36817627/ssl-certificate-missing-from-dropdown-in-sql-server-configuration-manager/36823345#36823345]]