How to install SQL server securing it with SSL communications. For Server 2000 or Server 2003. Issue study, design and implimentation document.
From: Ed Patterson (edtelecommuter_at_softhome.net)
Date: 06/25/03
- Next message: JT: "Re: Service Account"
- Previous message: cengiz: "Application Connecting"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 25 Jun 2003 10:55:58 -0400
Why would we want to do this?
1) We can't trust machine in the 'user LAN' to not get their machines
compromised and thus have these trojaned machines sniff SQL connect string
off the wire.
2) Local politics and or competency may be preventing Administrators and
local machine administrators from updating all machines on the same wire
segment as the webserver or SQL server with windows patches.
3) Their may be no host intrusion detection or local Network intrusion
device on the webserver/app server and or SQL server.
What all three above items have in common is an appreciation that internal
attacks on your critical infrastructure are just as likely as external
attacks from the internet.
For those who would be concerned that their NIDs signature detection
appliances will be made worthless by this procedure.
Maybe, you should get a NIDS that can understand SSL encrypted protocols,
perhaps they should also understand IPV6 while were at it.
Most early invented NIDS do NOT understand SSL or IPv6 traffic because they
haven't bothered to write a decoder and a check in procedure for the SSL
certificate.
I would assert you probably should upgrade to SNORT which now understands
IPv6 and add SSL dump and or an SSL application proxies to your
infrastructure.
SSLdump is used to decode SSL traffic the decoded traffic can be applied
against the NIDS signatures to detect attacks coming through n the SSL
stream.
I would also advise this is where Host intrusion detection takes over,
because unless your NIDS talks to your routers to make new ACL statements
the NIDS isn't going to protect you the way a HIDS could in vault mode.
If you rely exclusively on an IDS signature based engine that's crippled by
not being able to decode Server authentication SSL Certificates or IPv6
tunneled traffic then these issues are those of base IDS competency not our
focus of securing the SQL connect strings against internal snooping.
SQL / webserver connect strings are sent in CLEAR plain text over the wire,
so NOT using one of the secure login techniques such as IPSEC TSL or SSL is
security by easy to defeat obscurity.
The following procedure works for SQL server 2000 on windows 2000 server
professional or windows 2003 server enterprise.
Planning phase.
SQL server will be running on a user account. Pick a new user account name.
Download SQL server Service pack 3a
My advice is to create a new instance of SQL server on a new box and Migrate
your databases to it.
Plan to be able to Sniff network packets on the SQL server, Use ether peek,
netboy or the built in windows 200x packet monitoring program.
Plan to have a certification Authority installed, and make sure its NOT on
the same box as the sql server or web server.
Plan to secure the Certification authority after the certificates are
requested issued and installed. My favorite way to protect these is with a
PGP disk.
See www.pgp.com for pgp version 8 which is now XP / 2003 compatible.
Alternatively, you can use a zip file with an unusually long and ugly
cryptic password.
Note: From the moment in time the operating system is installed and
recognizes your NIC, the new machine should be behind a NAT SPI enabled
firewall.
Fully patch the machine with windows updates prior to installing SQL server.
Create the new user account. This account SHOULD NOT be added to the
administrators group during the installation or after.
Change logon security audit policies to success and failure and set
Anonymous enumeration of SAM and shares to disable. Turn file sharing off.
You can turn the above setting on later if you have to after the
installation is complete and works and is tested.
Plan to either remove or not install any SQL example or webserver Example
files and or example applications.
Login to the unprivileged user account interactively.
Right click the SQL setup.exe and RUN AS Administrator.
When prompted to select Windows authentication VS Mixed authentication,
select MIXED.
When prompted to run the SQL server and monitor as local service or a
selected user, enter the use name and password of the user account created
to host the SQL server in the planning phase.
Complete the SQL installation.
Unzip and ready the SQL SP3a service pack.
Note the setup.bat batch file used to start the SQL update process can NOT
be 'Run As..
The batch file points to points to the executable program
X86\setup\setupsql.exe
Right click on SetupSQL.exe from an explorer window and 'Run As'
Administrator.
Complete and verify all steps of the update patch run correctly.
Note, SQL SP3a automatically does every step in the following Microsoft
article.
HOW TO: Change the SQL Server Service Account Without Using SQL Enterprise
Manager in SQL Server 2000
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B283811
Installing SQL SP3x is required and it saves your from having to manually
edit the registry link and ACL's describes in the above article.
Pitfall 1, FQDN for Dummies.
Your Cert server must be installed with the name shown identically to the
system properties Network Identification Tab Full computer name.
Use the HTML web page on the certificate server.
Pitfall 2, undocumented Microsoft security feature.
If you get stuck on the Certificate request page with the 'down loading'
message which never go's away, run the following fix.
C:\WINNT\system32\certsrv\cafixweb.exe
Note: Your still logged in as the unprivileged user.
On the Cert server, click start, programs, administrative tools,
Back on the SQL server go back to the web page start http:// Open the server network SQL utility, and click on the forced encryption
Testing.
Hope this helps.
Email me at edtelecommuter@softhome.net
Request a server signing certificate, select 'Request Certificate' <next>
from http://
Click 'advanced request' <next>
click 'Submit a certificate request to this CA using a form' <next>
Enter the Simple FQDN name in name, this is the same name as what shows up
when you look at the Control panel, system properties, Network
Identification Tab, Full computer name.
Select the intended purpose as 'Server authentication Certificate'
Select the CSP as 'Microsoft RSA Schannel Cryptographic Provider'
Select a key size, I've tried 512 and 1024 bit keys successfully, not that
others wouldn't work as well.
Enter your email
Because the company, department, city, state, country is defaulted by the
Cert server I don't bother changing these fields.
Click <Submit>follow through to the end of the process where your told you
would check back.
Certification authority.
Right click on the new item in the Pending subfolder, select all tasks, and
select issue.
server>/certsrv
Check on a pending certificate, Retrieve and install the certificate using
the web interface.
protocol.
On the SQL client open the SQL client network utility on the SQL server and
the remote SQL client and enable forced encryption protocol.
Stop and start the SQL server.
Open your packet monitoring program. I suggest isolating yourself from
network spam, then run the packet sniffer when forced encryption protocol is
NOT set, and when it is set and compare the differences.
You should see the obvious difference between the SSL encrypted and non
encrypted session.
Attempt to connect to the SQL Server.
Consider this my personal RFC on SQL security.
Comments notes revisions greatly appreciated.
By the way I am available to secure YOUR security infrastructure. :)
Please feel free to contact me if your have a request and would like to make
travel contract plans
Full service infrastructure protection security network application
services.
Relevant Pages
... This digital signature takes the form of a certificate ... SQL server 2000 implements SSL. ... Enable SSL Encryption for SQL Server 2000 with Certificate Server ...
(microsoft.public.sqlserver.server)
... This post describes issues concerning the implementation of SSL ... certificates on SQL Server 2000 clusters. ... cluster and failover is working, ... DC is running a certificate authority, and that the CA is configured ...
(microsoft.public.sqlserver.clustering)
... This post describes issues concerning the implementation of SSL ... certificates on SQL Server 2000 clusters. ... cluster and failover is working, ... DC is running a certificate authority, and that the CA is configured ...
(microsoft.public.sqlserver)
... Clearing the certificate had no success. ... I suspect there's a problem with the permissions of the SQL Server service ... SQL Server Database Services 2005 ENU SP2 ... This is an informational message only; no user action is ...
(microsoft.public.sqlserver.setup)
... Clearing the certificate had no success. ... I suspect there's a problem with the permissions of the SQL Server service ... This is an informational message only; no user action is ...
(microsoft.public.sqlserver.setup)
