[ VIA Dragos KyXsPaM] url: http://www.sqlsecurity.com/faq.asp ********************************************* ** SQL Server Security FAQ ** ********************************************* http://www.sqlsecurity.com Why is SQL Server security important? 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. What different network protocol libraries does SQL Server support and which should I use? SQL Server Network Protocol Libraries -- Protocol library Notable Weaknesses Encryption -- Named pipes Uses NT SMB ports (TCP 139, UDP 137,138) to communicate. These are usually blocked in any decent firewall implementation, but that can be an advantage if all access is internal. User names, passwords, and data can be sent unencrypted (not using Integrated Security) for anyone with a packet sniffer to see. No -- IP Sockets If you leave the TCP port with the default value of 1433, you open yourself up to port scanners that can immediately spot the SQL server. Packet sniffer threat. No -- Multi-Protocol Client must support NT RPCs; in a heterogeneous environment this can be trouble. Uses random TCP ports by default but can be configured to use fixed ports to ease firewall implementation (see KB article Q164667). Make sure the encryption option is selected; it's not selected by default. Yes -- NWLink Packet sniffer threat. No -- AppleTalk (ADSP) Packet sniffer threat. No -- Banyan Vines Packet sniffer threat. No -- My recommendation: if you can get away with using Integrated (NT) Security over Named Pipes or Multi-protocol, then do it. If possible, try to use multi-protocol and enable encryption. If you can't use either of those, then use IP Sockets, change the default TCP port to something else, and pray you don't get a packet sniffer on your segment. Also, consider using a Web server or COM components as the business object layer for an application and use a secure channel between the middle tier and the SQL server. This will remove some of the client connection issues and allow you to focus on encapsulation of business logic as well. There are several third-party products that can encrypt traffic on a segment - look into them. What are the various security modes for SQL Server and how do they work? Just when you thought you were getting a handle on SQL Server's security modes, Microsoft goes and makes changes in SQL 7. Never fear, these changes shouldn't cause you too much distress and may even make your life a bit easier. The security mode defines how SQL Server will authenticate users attempting to make use of its services. Check the following table for a description of these modes and a breakdown of the differences. Security Modes SQL Server 6.5 SQL Server 7.0 changes Standard Logins are defined within SQL Server and given passwords SQL Server logins are separate from Windows NT accounts Exclusive Standard Mode security is not available in SQL Server 7.0 Integrated Use the Security Manager to create SQL logins for NT Server user accounts Users do not have to specify a separate login and password when they connect to SQL Server Passwords are never stored in applications and never transmitted in plain text over the network SQL Server gains the benefits of using NT to authenticate users and use features such as account expiration and lockouts Requires Named Pipes or Multi-Protocol libraries Now called 'Windows NT only' mode SQL 7 now supports integrated security across all network protocol libraries Only works on NT - Windows 95/98 installs do not support this mode (duh!) Can integrate directly with NT groups for added ease of administration. (Make note of the special BUILTIN group for assigning groups on the local machine. - See Books Online for more info.) Mixed Offers the features of Integrated but can fall back to support clients that cannot establish trusted connections Let's face it - this mode may expose you to the weaknesses of both modes. Tightening the security of both modes is a lot of work. Now called 'SQL Server and Windows NT' mode Same deal - use with caution. Why use a weak security model if you don't have to? Bottom line: If you can get away with 'Windows NT only' mode, then use it. 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. See Also http://www.microsoft.com/technet/SQL/Technote/secure.asp for Microsoft's notes on the subject. What are some things I can do to secure my SQL Server? Please, if you do nothing else in this FAQ, change the password of the "sa" account to something besides null-preferably something of at least eight characters and not found in any dictionary. Without fail, I find multi-million-dollar corporations leaving this key lying around. Remember, the sa account is the SQL Server equivalent to the Windows NT Administrator account. If it's compromised, your world will crumble sooner or later. Some other general suggestions: Don't use port 1433 on IP sockets. Also change the multi-protocol port if in use. Don't embed 'sa' passwords in applications such as VB/Delphi apps, global.asa files, or batch files (for BCP or whatever). Secure the SQL Server error logs with tough NTFS permissions - I mean *SECURE* Don't leave the xp_cmdshell extended stored proc on the server if you don't need it Place SQL Servers that transmit vital information on an isolated segment and only allow connectivity through middle-tier applications. 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" Then: Xp_cmdshell "net localgroup /ADD Administrators testuser" 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 (137, 138, and 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 TFTP to download a NetCat litener to the machine and then configure it to listen and transfer? Be creative - hackers are. 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. Can you name some websites that deal with NT and Internet security? http://www.iss.net/. Internet Security Systems offers a great database of exploits for all platforms. The only issue I have is that its explanations are somewhat brief and almost never include source code or links. The information it has on SQL Server security is excellent compared to other Internet resources on the subject. http://www.ntsecurity.net/. This is a good place to keep up with general NT security issues that arise. The good thing about this site is that it usually includes source code of exploits and gives plenty of links to find more information. Very little SQL Server info is found here, but remember that securing the box starts with the OS. http://www.microsoft.com/security. The de facto source for NT and SQL Server security. Generally, people should submit new security issues to Microsoft first so it can issue a fix before the world finds out about it. What good does it do to tell everyone about a new exploit if you have no ability to fix the problem? http://www.ntbugtraq.com/. Subscribe to the mailing list here. It's an absolute requirement if you want to stay on top on NT security. Since SQL Server is an NT-only application server, if any new issues should arise, they'll be discussed here. Other Links of Interest: Need a free packet sniffer? He're and excellent solution http://netgroup-serv.polito.it/windump/ Rain Forest Puppy has done more for NT security (and sercurity in general) than anyone else I can recall in recent history. His constant attacks on SQL (of all varieties) security should keep us all on our toes when writing code. Do yourself a favor - watch, listen, and learn from this guy. http://www.wiretrip.net/rfp/2/index.asp What are some Knowledge Base articles that will help me secure NT? At http://support.microsoft.com/support/search. Q166992-Standard Security Practices for Windows NT. Q195227-SP4 Security Configuration Manager Available for Download. Q214752-Adding Custom Registry Settings to Security Configuration Editor. Q143164-INF: How to Protect Windows NT Desktops in Public Areas. Q161990-How to Enable Strong Password Functionality in Windows NT. Q155363-HOWTO: Regulate Network Access to the Windows NT Registry. Q143474-Restricting Information Available to Anonymous Logon Users. Q161990-How to Enable Strong Password Functionality in Windows NT. Q143475-Windows NT System Key Permits Strong Encryption of the SAM. Also, an excellent Web page by Stefan Norberg at http://people.hp.se/stnor offers a white paper with a complete description and set of tools needed to create a bastion NT server. Using the techniques found there, you should be able to secure the OS to a very high level. Make sure that when you configure the TCP ports, you allow TCP/IP to communicate with the port(s) SQL Server is listening on if using IP sockets. -- Dragos Ruiu dursec.com ltd. / kyx.net - we're from the future gpg/pgp key on file at wwwkeys.pgp.net