Common Security Concerns

As I’m being kicked off the soapbox, let’s talk about some security concerns with SQL Server. By default, SQL Server allows any Windows user in the local Administrators group to have administrative access to the SQL Server. This means that user can do anything that SA can do.

In most companies, the person who administers Windows 2000 is not the DBA who maintains the SQL Server. This login is called BUILTIN\Administrators in the Logins group in Enterprise Manager. This login is by default given sysadmin rights, which in turn gives any local administrator full access to all of your SQL Server data. The login could cause harm to your system and should be deleted or be given limited access. If you feel that it is necessary to grant your NT administrators this type of access, grant them under a different group other than BUILTIN\Administrators.

One interesting extended stored procedure you can use to trace who has access to a given group is xp_logininfo. This extended stored procedure will look into a Windows group and tell you who is assigned to it. For example, to find out the members of the BUILTIN\Administrators group, run the following command:

EXECUTE master..xp_logininfo 'BUILTIN\Administrators', 'members'

This would return the following results (will be different on your server based on the logins you have):

account name       type     privilege mapped login name   permission path ------------------ -------- --------- ------------------- ------------------  DOM\Administrator  user     admin     DOM\Administrator   BUILTIN\Administrators DOM\bknight        user     admin     DOM\bknight         BUILTIN\Administrators

start sidebar
In the Trenches

After you delete the BUILTIN\Administrators account, your SQL Server Agent (and potentially SQL Server) will no longer start if you're starting the services with the system account. This is because the account that starts SQL Server Agent must have the sysadmin role. If you try to start the account without the proper permissions, you'll receive the following error:

 [000] SQLServerAgent must be able to connect to SQLServer as sysadmin, but '(Unknown)' is not a member of the sysadmin role.

To fix the problem, simply add a domain account as a SQL Server login and give it sysadmin rights. Then configure SQL Server Agent to start with this account. Keep in mind that you will not be able to use the system account anyway if you'd like to perform any type of replication.

I would highly recommend that you change the login that starts the SQL Server services through the Enterprise Manager tools. If you do this, all the rights are assigned to the user automatically for Windows and the account will be created in SQL Server with the appropriate rights.

end sidebar

Debugging Tip 

A bug exists in SQL Server 7.0 (prior to SP3) and 2000 that allows third-party vendors that provide extended stored procedures to potentially cause harm to your system and gain administrative access to your server. The bug exploits an API called srv_paraminfo() to obtain the level of access of the account that starts SQL Server. If you have followed proper security, the account that starts SQL Server won’t have access that could result in any harm to the machine. However, the account could still cause harm to the databases, because the account that starts SQL Server is a sysadmin. The extended stored procedure could also overrun the buffer, crashing the server. You can download a patch to fix this bug from http://support.microsoft.com/support/sql/xp_security.asp.

Default Databases

Make it a practice to change the default database for each of your user-defined logins to prevent users from harming your master database. If you change the default database, users must think about changing to the master database before running scripts. For example, by default when you open Query Analyzer you are directed immediately to the master database. I can’t tell you how many times I’ve created stored procedures in the master database by accident after a late night because of this. There is no reason for any user to have the master database by default. To change the default database in T-SQL, issue the sp_defaultdb stored procedure as shown here:

EXECUTE sp_defaultdb 'login', 'defaultdb'

Protocol Vulnerabilities

Typically, companies use the default settings for protocols and for the ports SQL Server listens on. It is a good idea to take control of these settings instead of using the defaults, because they are predicable to hackers. You can adjust the protocols your server uses, and the port it listens on during setup, by using the SQL Server Network utility tool in the SQL Server program group.

By default, Named Pipes and TCP/IP are both enabled in SQL Server 2000. You can select TCP/IP and click Properties to hide your server from scanners trying to enumerate all the SQL Servers in your network. In Table 3-3, you can see some of the weaknesses in each of the communication protocols.

Table 3-3: Protocols that Connect to SQL Server

Protocol

Major Weaknesses

Named Pipes

User names and passwords can be transmitted unencrypted. Network packet sniffers can read this if it is unencrypted.

TCP/IP

If you stay with the default of port 1433, you are vulnerable by default to network scanners finding your server. You are also vulnerable to packet sniffers.

Multi-Protocol

This method encrypts data, but does not select the best encryption by default.

Change your default port from 1433 to another port for the TCP/IP protocol. There are programs easily available to find any server listening on port 1433 because it is a commonly used port by SQL Server and very predictable. Even though changing your port from 1433 to a different port won’t protect you from port scanners, it will make them slow down or even discourage the hacker from proceeding with the scan. Before you change the SQL Server port, make sure you have properly tested your application. Changing your port is not an easy task since it will involve the application groups and the firewall group.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net