Securing Your SQL Server 2005 Express Edition Server
Beta V Corporation
Microsoft SQL Server 2005 Express Edition Beta 2
Summary: Get introduced to SQL Server Express, learn how to install and configure it in a secure manner, plus get information on the basics of SQL Server security. (15 printed pages)
What is SQL Server Express?
What Is a "Secure" System?
What Are the Issues?
How Does SQL Server Express Address These Issues?
Installing SQL Server Express Edition
Connecting (Gaining Access) to SQL Server Express
SQL Server Security General Guidelines
What is SQL Server Express?
The SQL Server 2005 Express edition is the replacement for the Microsoft Desktop Engine (MSDE) edition of Microsoft SQL Server. Its architecture has been completely redesigned to enable you to install and use it as you would Microsoft Access/JET databases—but without the problems associated with that approach. SQL Server 2005 Express Edition goes a long way toward building a better solution for applications that need:
- A replacement for JET databases. That is, a DBMS that can be taken over by an IT department if needed, one that meets HIPA security requirements, one that uses all of the power of SQL Server to protect data and referential security and do all of this regardless of how the user mistreats it. (HIPPA or HIPAA (often shortened to HIPA) refers to federal legislation that requires robust security and access protections be placed on databases storing personal healthcare information.)
- A DBMS that can scale from a single user to several dozen users without upgrading to SQL Server Standard edition—and without having to worry about a governor degrading performance when it's needed most.
- A DBMS that can work as easily on a small Web site as well as in a client/server configuration.
- A DBMS engine that can be easily installed and updated in place when service packs are made available. This means setup routines that are easily integrated into your application's deployment scripts.
- A DBMS that can be accessed by simply pointing to a DBMS file installed with, or passed to, an application. Because SQL Server Express is designed to permit databases to be attached on the fly, it's easier than ever to use "loose" SQL Server MDF database files and deploy them with your application. This makes it far easier to deploy a stand-alone SQL Server Express database .MDF file, as could be done with JET databases.
- A standard way to refer to a shared instance of SQL Server. When SSE is installed, by default it's installed with the same instance name: SQLEXPRESS. This means your application's connection string can more easily target SQL Server Express whether it's installed on the local system or on the local area network, assuming the application setup routines take advantage of this feature. I'll talk about instance issues a little later.
My new book, Hitchhiker's Guide to Visual Studio and SQL Server 2005 (Addison Wesley), will have an entire section on SQL Server 2005 Express Edition, but for this article, I'll limit my focus to managed security using SQL Server 2005 Express Edition Beta 2. Along the way I'll discuss:
- What is a "secure" system? What does security mean for a small system?
- What issues are MSDE developers facing?
- How does SQL Server Express address these issues?
- How do applications gain access to SQL Server Express databases?
- How do you protect your SQL Server Express database?
Note At the time of this writing, the SQL Server 2005 Express Beta 2 should not be used in a production system, exposed on the Web, or used outside the EULA restrictions.
What Is a "Secure" System?
Before we wade hip-deep into a discussion of the technical merits of SQL Server 2005 Express Edition and how to configure its security features, I think it makes sense to define what security really means. Sure, for a small business or departmental system, when the data server is compromised or its data is lost or corrupted, the company is just as vulnerable to failure as a large one. SQL Server Express can reside on a Web server to provide SQL Server services for ASP applications. Therefore, uptime, reliability, and security also mean the ability to expose information to Web server applications, but not be vulnerable to Web-sourced attacks. SQL Server Express is also ideal for the "paradeveloper" who writes code and builds applications as a sideline. These doctors, lawyers, receptionists, and taxicab drivers all need a simple, safe and reliable way to store and retrieve data without having to worry about what's being done for them behind the scenes.
Security also includes those steps the application designer and developer take to prevent data loss, whether that loss is due to accident, neglect, or malicious attack. Security means keeping out those that should not have access to the data, and protecting the physical files and the system itself. It means making backups and being able to perform restores seamlessly. With SQL Server Express systems, this is especially challenging, because as often as not, there won't be a dedicated systems administrator or IT department to step in and perform periodic backups or put the system back together from the pieces when it falls over. Having a secure system means you keep your job (and perhaps get a promotion) when problems occur and your application recovers quickly, quietly, and efficiently. I'll point out a number of things you can do to make your applications more durable, less prone to attack, and easier to maintain.
What Are the Issues?
I've been extolling the virtues of the MSDE edition of SQL Server for quite some time. That's because I'm convinced that MSDE is a far better solution for applications that need a "few-user" data store, or where an application does not have access to a remote DBMS engine. While MSDE has been widely adopted by a large number of serious businesses, they often have to deal with a number of issues on their own—implementing non-standard solutions that sometimes conflict with other companies' attempts to solve the same or other issues. These include:
Deployment: How is SQL Server installed along with an application? There is a litany of related issues here. For example, what if SQL Server is already installed? What if the instance name collides with other existing instances? Should the application share an existing SQL Server instance or create a unique instance? What happens when the application that installed the shared SQL Server instance is uninstalled—should it also uninstall the SQL Server instance? If so, what happens to the other databases that instance is hosting?
Security: If you choose to share an instance of SQL Server, what password should be used for the SA? How can user accounts be setup? Should the application simply use integrated security managed by the domain controller? What if there is no Active Directory? How are databases installed on the target MSDE server? After installation, is the database visible to other applications on the server? How can applications hide proprietary data?
Performance: MSDE uses a governor that limits the number of simultaneous operations on the server. What if your single-user application needs to perform several operations at once but the governor kicks in and slows it down? Frankly, I'm not sure this issue is that widespread. I've heard very few people complain that the governor kicked in and made their application run too slowly. Sure, I've heard of applications not running particularly fast, but these were (generally) caused by brute-force queries or "challenged" database implementations.
Scalability: MSDE databases are limited to 2GB. What if you need to store more data than that? Does it mean you must upgrade your target systems to use a SQL Server Standard Edition that might cost more than the systems that expect to use it? Again, most of the complaints I've heard about involve when people stored binary large objects (BLOBs) like documents or pictures in the database. Once they replaced the BLOBs with a path to the BLOB file, their databases shrunk down to a reasonable size.
Tools: The MSDE version of SQL Server is the "deployment" configuration. As such, it does not include any tools needed to administer the server or the databases it manages. I usually recommend that developers buy the $49 (SRP) Developer Edition that includes the full suite of tools to manage their MSDE databases. However, due to licensing restrictions, these tools cannot be deployed with an application. This means developers have to build their own client-side tools or simply build needed functionality into their deployed applications.
Administration: Regardless of how it's done, applications must take on quite a few administrative responsibilities. This is especially true for SQL Server Express systems where there is no "SA" (system administrator) at the keyboard. These admin responsibilities include managing the Login Account(s), permissions, backup, restore, and log maintenance. Your end user is not usually capable of, and should not be trusted to perform, these operations—it's up to your application to do so. While JET databases needed periodic compression or repair, MSDE (and any SQL Server database) needs to periodically back up (and dump) the logs and database. This issue becomes fairly significant when the database is not managed centrally, where administrative and maintenance tasks can be more easily managed. Again, this is up to your application to do.
Service Packs: Since MSDE is often embedded in applications, users might not know that they have an instance of SQL Server installed. As such, they aren't aware that they might need to post a SQL Server service pack to protect their data and systems from attack even if they see it on the 5 o'clock news. To help prevent some of the problems caused by worms and other attacking viruses, MSDE SP3(a) disabled network connectivity so applications are unable to connect to the server over the intranet (or Internet). The problem is that the service pack was not applied to many systems because users did not know it was necessary, nor did they know how to apply the patch. This issue notwithstanding, posting SQL Server updates to MSDE installations is problematic, as the Microsoft upgrades don't always work with custom setup scripts used to deploy MSDE applications and databases.
How Does SQL Server Express Address These Issues?
Developers, architects, and IT managers all over the world have been discussing the aforementioned issues for several years. While there aren't solutions to all of these problems, SQL Server Express has addressed many of them by making some fairly fundamental changes. Before you get caught up in the differences, it's important to know what hasn't changed. SQL Server 2005 Express Edition is still free (with the usual licensing and use restrictions), it still supports subscriber replication and virtually all of the same features as MSDE. The new SQL Server Express version cannot host Reporting Services, but it can be a data source for a server hosted on SQL Server 2000 Standard Edition. (For more information on SQL Server Reporting Services, see the Boost.net Web site.) By default, setup still disables the ability to expose the SQL Server Express instance to the network (as was first implemented in MSDE SP3). Let's take a closer look at SQL Server Express to see how it addresses each of the issues
SQL Server Express is designed to be downloadable over the Web and installed on a user system just like any other systems software. (This assumes that the system administrator installs SQL Server Express.) You can use the interactive Setup program (as I describe later), or run a command-line Setup executable. With the "quiet" mode, the user need not see any SQL Server Express setup dialogs at all.
When you install SQL Server Express, by default the Setup program attempts to create a common SQLEXPRESS instance. If it's already in place, you'll be given the choice of abandoning the Setup or choosing another instance name. The idea here is to get applications that use SQL Server Express to share a common instance, not create one of their own. This makes application configuration easier and reduces the memory and disk footprint on the user system as well.
If you uninstall your application, it's also a good idea to uninstall any unique SQL Server Express instance you installed. However, Microsoft recommends that you leave any SQLEXPRESS instances in place unless you're sure the system does not have any other dependent applications that use it. One way to determine this is to search the Master database for other databases that other applications might have attached or created.
By default, SQL Server Express is configured to protect your data. As you install, you're given the opportunity to further tighten security or loosen it depending on your requirements. One of the first decisions you'll have to make is to choose how the setup utility configures the SQL Server Express instance. An "instance" is simply a copy of the program. Starting with the SQL Server 2000 version, SQL Server permits you to install several independent instances of the server. Each instance is treated like a separate entity: each has its own Master database, its own security configuration, and its own place on disk and in memory. When SQL Server Express is set up, each application (or you) must to decide if it can coexist with other applications using a shared instance of the server, or whether it requires its own independent instance. There are security issues associated with each configuration, as I outline below. Note that SQL Server Express permits you to install up to 15 instances, but I don't expect folks to install more than one or two except in very special circumstances.
Installing a common instance
By default, SQL Server Express assumes that you want to create (or use) a common instance named "SQLEXPRESS". You can also name a "common" instance, but this assumes that all programs you install know this unique name. If you keep the default name (SQLEXPRESS), other applications can automatically share this common instance. With this approach, all databases are managed by a single, shared Master database, and there is one SA password, which need never be revealed. When using a common instance, you might be able to see other installed databases and other applications might be able to see your database—unless you make sure that appropriate permissions are put in place. Generally, for home, hobbyist, or small office implementations, you don't usually have to worry about one application disturbing data in another database. If you install a single common instance, only one set of SQL Server DLLs, caches, and other memory-resident structures are loaded into memory. This means only one SQL Server instance consumes CPU resources.
Installing an independent instance
During setup, if you set the instance name to your own value, the installation program creates an entirely independent version of SQL Server Express. This instance has its own Master database, its own files, DLLs, and memory footprint and its own SA password. Each independent instance starts a separate SQL Server service (program) that consumes CPU cycles, in addition to any other instances that might be installed. While this approach gives you more security in the sense that only those granted access to this instance can see the databases it manages, it's more expensive to implement and maintain. That's because each instance duplicates DLLs, caches, and other in-memory structures.
Installing the default instance
Another approach is to remove the instance name during setup. In this case, SQL Server Express is installed as the default instance, assuming there isn't already a default instance installed. Only one server instance can be installed in this way. Again, if this is the only instance installed on your system there is very little difference between the other configurations, except when it comes time to connect to SQL Server Express, as I discuss later.
Choosing the System Administrator's Password
The SA password is the key that unlocks the entire database. The system administrator is permitted to do anything with or to the databases or the information they contain. The SA can add, change, or remove databases—all without anyone knowing the changes have been made. It's critical that this password be set correctly and protected.
When you install SQL Server Express using a common instance, there is only one SA password to worry about. Since the SA account is only accessible when you choose to install using Windows Authentication, the SA password need never be revealed. In any case, when you install SQL Server Express you're asked to provide an SA password, but this could be set to a random (hidden) value in the released versions.
Microsoft recommends that you configure your SQL Server Express instance to use Windows Integrated Security Authentication. This means the computer and Windows domain system administrator accounts are granted full SA access to the SQL Server Express instance. Sure, you'll need to be a computer or domain administrator to perform maintenance, install databases, and perform operations as simple as changing the database table values. This does not mean everyone who uses SQL Server Express should be an administrator. It does mean that, as part of the setup regimen, you'll need to create a "user" or application Login and set appropriate permissions on the tables, views, functions, and stored procedures that your application needs. I discuss this in more detail later.
SQL Server Express has abandoned the concept of a "governor". Frankly, I've rarely seen the governor slow any MSDE system down, but by dropping the governor, Microsoft has removed a point of confusion about the scalability of the SQL Server engine. SQL Server Express has ways to limit scalability. As configured in the Beta, SQL Server Express can only address 1GB of system RAM in the buffer pool. This limits the number of data pages and procedures in the RAM cache. Any SQL Server pro can tell you that the easiest way to improve performance is to add memory to the cache. Limiting visible RAM to 1GB means that you'll (eventually) run out of performance as you add load to the SQL Server Express instance. Does that mean SQL Server Express can support 1000 users? Sure, if the load placed on the SQL Server Express instance is not that great. In the same way, 10 users could bog down SQL Server Express, especially if the application is not written very efficiently.
SQL Server Express is also limited to a single processor instead of being able to run threads on additional processors (up to two) if your system supports it. This limitation also tends to scale back the upper limit of the performance you can expect from SQL Server Express.
When an application using SQL Server Express ends, SQL Server does not shut down. There is no auto-shutdown option in the SQL Server Express version. Because of this, the SQL Server engine is left in memory and continues to consume system RAM and CPU resources even after your application has ended. It's possible to write SQL Management Objects (SMO) routines to shut down the SQL Server Express instance, but this needs to be done only when you're sure it's not being shared by other applications.
While MSDE databases were limited to 2GB, SQL Server Express database files are "limited" to 4GB. This means you can store twice as much data as before. Frankly, this puzzles me. I've worked with large corporate databases on a mainframe that fit nicely on a single 40MB disk pack. I guess people like to use the database to store a lot of documents and pictures of their pets. As with MSDE, the log file size is not limited—at least artificially. You still need to back up and truncate the logs periodically as I discuss later.
Microsoft has changed its approach to the tools as well. Even if you don't count the new GUI setup, when you download the SQL Server Express Beta 2, a new version of OSQL, SQL Computer Manager (MMC snap-in) and SQLCMD command-line tools are included to help manage the SQL Server Express instance. In addition, Microsoft plans to have a new GUI tool (tentatively named SQL Express Manager) to perform the initial configuration and periodic maintenance of SQL Server databases. This tool, which will soon be available as a separate download, is basically a tool not unlike SQL Query Analyzer to do user account setup and maintenance and help write, test, and debug SQL queries. You won't be able to connect to SQL Server Express with any other tools, including Management Studio or SQL Enterprise Manager. However, I expect that by the time it ships, SQL Server Express will be accessible from any of the current tools.
All of the tasks you had to do to administer MSDE have to be done with SQL Server Express, just as they have to be done with other versions of SQL Server. I would love to see an automated log backup script that periodically dumps the database and logs, and then truncates the log. Perhaps that's something that an enterprising third party needs to create. Until then, I recommend developers build these admin tasks into their applications and use SMO to perform these needed maintenance functions and use Windows Scheduler to help.
SQL Server Express can only be installed using the Windows Installer (MSI) installation package files. Unlike MSDE, you won't be able to create custom MSM setup scripts. In other respects it's the same as MSDE, so you'll still need to be prepared to update the SQL Server engine via the traditional Service Pack means. The folks at Microsoft are acutely aware of the issues in this regard and are still formulating a better strategy.
Installing SQL Server Express Edition
Unlike MSDE, which does not support any form of a GUI Setup utility, SQL Server Express permits both command-line setup as well as a GUI version. This version of setup is familiar to developers who use the Standard Edition version or greater. However, early in the process, the SQL Server Express GUI Setup program exposes the dialog (as shown in Figure 1) that asks if the user wants to set the Advanced Configuration options. By default, Setup configures the SQL Server Express instance being installed to use Integrated Security and disable all access to the TCP ports and external protocols. This means you won't be able to access the SQL Server Express instance from other systems, or by using SQL Server credentials, unless you change the advanced configuration options.
Figure 1. Capturing registration information for the SQL Server Express GUI Setup utility
Choosing the security mode
The SQL Server Express Setup utility permits you to set the type of security used by the server in the Authentication Mode dialog (as shown in Figure 2). As I discuss later, the default mode is Windows Authentication, which validates user credentials against the Domain Active Directory database. It's a good idea to leave the default until you understand the security implications of switching to SQL Server Mixed Mode security. For example, mixed mode (SQL Server) security forces developers to figure out ways to hide the SQL Server credentials used by their application to prevent their use by unscrupulous hackers. Even then, it's still a good idea to stick with the default setting unless your design makes this configuration impossible.
Interesting Aside Where do hackers come from? At a Diligence Information Security conference in London, one study found that most "hackers" (those trying to gain unauthorized access to protected data) were from individuals within the corporate firewall—and most (by far) were on the company payroll.
Regardless of the type of security you choose, the Setup utility demands that you provide an SA password. While it says that you'll need to provide a "strong" password, this is really a function of the Domain Password strength settings. I encourage you to use a well-formed strong password, but it's not that important if you stick with Windows Authentication Mode. The utility won't let you leave it blank.
Figure 2. Setting the Authentication mode used by the SQL Server Express instance
Installing the SQL Server Computer Manager extension
The one and only tool that is installed with SQL Server Express is the SQL Server Computer Manager MMC snap-in. This tool can be used to manage the SQL Server services and enable SQL Server to be seen on the network. To install this component, select it while installing your SQL Server Express instance using the Features Selection dialog (as shown in Figure 3).
Figure 3. Installing the SQL Server Computer Manager extension.
Once your SQL Server Express instance is installed, the SQL Server Computer Manager can enable the TCP ports or appropriate network protocols by navigating to the "Protocols for SQLEXPRESS" node, right-clicking and then choosing Enable, as shown in Figure 4. In this case, I enabled the Named Pipes (Np) protocol. You'll also have to start the SQL Browser service to provide server name resolution.
Note Remember, the "Slammer" worm exploited the fact that most SQL servers are exposed on UDP port 1434. This means SQL Server Express won't be prey to this type of attack unless you enable the SQL Browser service.
Figure 4. Using the Computer Manager MMC snap-in component to enable network visibility
Once Setup is completed, the Setup files (which can contain plain-text or weakly-encrypted credentials, and other sensitive configuration information—basically the keys to your server) should be deleted or secured.
Connecting (Gaining Access) to SQL Server Express
Microsoft and I want you to break your dependencies on COM and the OLE DB providers in favor of using managed code. The SqlClient .NET Data Provider is still the best choice. If you must connect to SQL Server Express from COM-based applications using MDAC and OLE DB, you can do so, but you can't connect over shared memory providers, and you'll need to ensure that the SQL Browser service is started.
Since the default security setting is Integrated Security, you'll need to use Integrated Security=SSPI in the connection string, unless you change to mixed mode security. You still need to specify an initial catalog or Database in the connection string to point to the specific database your SQL is targeting. I also recommend use of the Application Name connection string parameter to uniquely identify your operations when you use SQL Profiler to monitor the operations your code executes.
Connecting Using AttachDBFilename
A new approach recommended by the SQL Server team is to add the keyword AttachDBFilename to your connection string. This is an unusual approach for typical SQL Server client/server front-end applications and is rarely if ever used for Web applications. As with any connection string addressing SQL Sever instances, you must point to the server by name (or IP address) and provide an instance name. In addition, when you point to a filename in the connection string using the AttachDBFilename keyword, ADO.NET (or ADO) tells the targeted SQL Server instance that you want to "attach" the referenced file to the server—thus registering the database in the SQL Server Master database in the process of opening the connection.
Once a database is attached, from that point forward, the server accesses the referenced file (.MDF) and its companion log file (.LDF) when you reference the database. Be careful because there's a catch here. You must specify the Database keyword in the connection string. If you don't, the server has no way to identify this newly attached database. Code Listing 1 shows an example of an ADO.NET Sqlclient.SqlConnection object being configured to attach and open an .MDF file.
Code Listing 1. Connecting to a SQL Server .MDF file using the AttachDBFilename keyword.
Try cn = New SqlConnection("Data Source=.\SQLExpress;" _ & "Integrated Security=True;Database=Biblio;" _ & "Timeout=60;" _ & "Application Name=SQLExpress Test;" _ & "AttachDBFilename=" & strFn) da = New SqlDataAdapter("SELECT AU_ID, Author, Year_Born from authors", cn) ds = New DataSet da.Fill(ds) DataGridView1.DataSource = ds.Tables(0) Catch ex As Exception MsgBox(ex.ToString) End Try
Tip The process of attaching a new database to Master can take far longer than simply opening it. Make sure you set the connection string Timeout keyword to account for this increased time.
Managing the attached .MDF database file(s)
Even though the process of opening the connection attaches a database, the database is not detached when your application closes the connection. Once attached, it's permanently installed in the SQL Server instance. This means the database itself is visible to any application with sufficient rights after your application ends. It also means you're responsible for maintaining a database file in the same directory with other application files. While the file is protected by Windows while SQL Server is running, it should not be overlaid with an "updated" version without first detaching the database. Again, detaching is not difficult. You can use the following command from SQLCMD, or use the SQL Server 2005 GUI management tools. Another approach is to use the AutoClose option that automatically closes the database file when all applications using the database have ended.
EXEC sp_detach_db 'MyDb' GO
Remember to save your database file on a local hard disk, not on a shared network server. It's dangerous to force SQL Server to perform physical I/O over the wire (if it's even supported at all) and it really hobbles your performance.
Unlike JET databases, it's easy to back up SQL Server database files (and there could be several), but the backup process involves sending a T-SQL command to SQL Server through OSQL, one of the tools, or through SMO. The database can be backed up at any time with any number of users logged on (and active).
Connecting directly to a named SQL Server Express database
A more typical approach to connecting to a named database on a named SQL Server Express instance (or any SQL Server instance) is to simply address the computer name followed by the instance name as shown in Code Listing 2. This approach assumes the Database being targeted is already registered with the SQL Server Master database.
Code Listing 2. Using "direct" access to a registered SQL Server database.
cn = New SqlConnection("Data Source=.\SQLExpress;" _ & "Integrated Security=True;Database=Biblio;" _ & "Timeout=60;" _ & "Application Name=SQLExpress Test;" _ & "AttachDBFilename=" & strFn)
Note SQL Server Express still supports the connection string notation of "(local)" or "." to refer to the "default" instance of SQL Server, but only if you install the "default" instance as I described earlier. I don't recommend this approach, as your SQL Server Express server might not be the original "default" instance on the server.
Using an alternative instance name
You don't have to install SQL Server Express using the default "SQLEXPRESS" instance name. I can envision several situations where using the default instance name is not a good solution. In this case, you'll need to use the Advanced Configuration options during Setup to choose another instance name and use that instance name in the connection string. A problem with this approach is that if your application setup utility does not know what databases are installed on the target server, your name might collide with an existing name—just as some other application installing SQL Server on your user's system might collide with the name you choose. That's why the common instance name of SQLEXPRESS is such an important innovation.
Using an alias
Another approach to connecting to a "common" server name from your application is to use an alias. That is, you can use the SQL Computer Manager to specify an alias for your SQL Server instance (as shown in Figure 5). In this case, I created an alias called "George" that I can use in my connection string. If the underlying server changes (as when I change from a test to production server), I simply change the alias and the application is redirected to the correct server.
Figure 5. Using the Computer Management utility to create an instance alias.
Managing integrated security with Windows Authentication Mode
When your connection string contains the keyword Integrated Security=SSPI, ADO.NET (or the data access interface you're using) uses Windows Authentication Mode. Behind the scenes this mode uses the NTLM (NT LAN Man) Windows NT Challenge/Response authentication protocol to validate the account credentials which uses encryption for secure transmission of passwords to prevent "snoopers" from picking your credentials off the wire. Each time a connection is opened (or reopened), the user credentials are re-verified against the domain controller (Active Directory) database. Microsoft recommends Windows Authentication Mode for most applications.
**Note **For more information on Security Support Provider (SSP) packages (like NTLM and Kerberos), see the SSP Packages Provided by Microsoft in the Platform SDK.
The test application I wrote to validate this code works fine (the bulk of the code is shown in Code Listing 1). That's because I'm logged in as an administrator, and as such my Windows account is granted system administrator rights on SQL Server. This is why you don't need to use the SA account or know what the SA password is when working with SQL Server Express. However, I certainly hope your end users won't be given admin accounts. When anyone logs into a Windows domain, they are granted rights determined by the domain's administrator. This information is stored in the Active Directory. These rights are not passed on to SQL Server unless you specifically grant them. This means non-administrators are (by default) not granted rights to the server or its contents and you'll need to set up users, groups, and roles to manage the database and its contents. The mechanisms for doing this have not changed in some time, and they are well documented in SQL Server Books Online. (More details are found in SQL Server 2000 SP3 Security Features and Best Practices on TechNet.)
Basically, there are four layers of security you'll need to establish and configure.
- The Windows domain account: Your system administrator needs to establish a domain account that includes a Login name and (strong) password—the user "credentials". This account is (by default) a member of the "Domain Users" group. Your administrator can set up other groups and assign users to these groups as needed. I usually set up "classes" of users that categorize them by the type of work role within the office they are assigned to. For example, I'll setup "Accounting Admin1" and "Accounting Admin Lead" groups and add specific Windows domain accounts to these groups. A single Windows user can be assigned to several roles.
- Physical security of the workstation and the user. If the workstation is left logged in while the user is away, or the user permits others to use their Windows account credentials, your security has already been penetrated. This layer is often overlooked. This is why Microsoft uses a key-access system to prevent access to systems when the user is not physically present.
- SQL Server Login: This is an account setup on SQL Server that's used to screen attempts to connect to SQL Server. Each account you add to this list dilutes the server's ability to protect the data as it permits additional Windows users to gain access to the server. When using Integrated Security (as we suggest), you'll still need to setup a Login account on SQL Server to permit access to the targeted database by a specific user or to a Windows Domain group (such as Domain Users). Each Login account is granted rights to one or more databases and is assigned a default database that is referenced if the initial catalog (Database) keyword is not used in the connection string.
- Database Users: The final layer of protection is managed in the database itself. In this case, you need to set up one or more database users that are granted rights to specific tables, views, functions, and stored procedures. You can even grant rights to specific columns if the need arises.
One approach to managing security accounts on any SQL Server database is to use SQLCMD. However, unless you're a database administrator (DBA) and are experienced with T-SQL, this can seem a bit daunting. Fortunately, you can use the SQL Server 2005 Management Studio that is equivalent to SQL Enterprise Manager to create database users, groups or roles. This tool is not included with SQL Server Express, so you'll need to use the Standard or Developer Editions for Microsoft-provided tools, or use one of the third-party tools. Once these roles are created, you can get the SQL tools to export these T-SQL commands to a script file.
Using mixed-mode security
Mixed Mode Authentication is an alterative to using Windows Integrated Security. In this case, the connection string UID and PWD keywords are validated against a SQL Server Login name and password. Since this technique bypasses Windows Authentication, it's seen as less secure. To use this security mode (and ignore our advice), you'll need to enable this Mixed Mode security during setup. To do so, when using setup batch files, you can set the SECURITYMODE command parameter to "SQL". This option is also available with the SQL Server Express interactive setup program and the SQL Server Express Manager (XM), of which a preview version should be available soon.
SQL Server Security General Guidelines
Security breaches on any system, whether it's a million-hits-per-hour corporate server or a million-hits-per-millennium small office system, can mean the demise of the company—or just your job. Since SQL Server Express systems assume that the application takes on many security roles, it needs to be prepared to manage SQL Server logins, perform periodic maintenance such as data and log backups, move backup stores off-system (and hopefully off-site), and other maintenance tasks as appropriate for your database use. Your application also needs to take steps to monitor the health of the server log, and report problems it encounters.
Developers not familiar with SQL Server often overlook a more fundamental approach to security, such as SQL Server's ability to protect objects right down to the column. In most serious office systems, the DBA (if there is one) immediately restricts access to the base tables. After that, the DBA establishes specific user and role accounts that have focused access to the database, enabling appropriate permissions on specifically applicable views, stored procedures, and functions. This way, if the user credentials are hijacked, the only way the data can be accessed is through these very easily constrained mechanisms.
This article introduces you to the new and improved version of SQL Server 2005 known as the Express Edition. I touch on the differences in SQL Server Express that make it easier to use and easier to protect, and discuss several security issues ranging from protecting the data, protecting the server, and protecting the physical system. I hope this overview encourages you to migrate your existing JET applications to the more secure and more stable SQL Server 2005 Express Edition.