How to plan SQL Server CE, SQL Server 2005 Compact Edition, or SQL Server 2005 Mobile Edition security for mobile devices in IIS and SQL Server 2000 Windows CE Edition

This guide assists you in developing a Microsoft Internet Information Server (IIS) and Microsoft SQL Server security plan for mobile devices. As mobile applications begin to increase in number, a solid plan can help avoid some of the pitfalls in security. The article examines various authentication methods for IIS and SQL Server. Because SQL Server CE, SQL Server 2005 Compact Edition, or SQL Server 2005 Mobile Edition connects to SQL Server through IIS, this article discusses both security environments for mobile SQL Server applications.

For a more detailed description of SQL Server CE and IIS security, refer to the following white paper by Kevin J. Boske of Microsoft:

Choosing an authentication method for IIS

IIS supports three different authentication methods:

• Anonymous
• Basic
• Integrated

Microsoft Windows CE 2.x does not support the integrated authentication method while Microsoft Windows CE 3.x supports all methods.

A description of each method follows.

Anonymous

This type of authentication is just as the name implies. The user is anonymous and has access to all resources on the IIS anonymously. Microsoft does not recommend this form of authentication for SQL Server CE, SQL Server 2005 Compact Edition, or SQL Server 2005 Mobile Edition because it is not secure.

Basic

When you connect to a server that requires basic authentication, SQL Server prompts you with a Login dialog box. You can login and gain access to any resources allocated to your logon. However, with basic authentication, your username and password are passed over the network in Base64 encoding, which is easy to read. Because basic authentication is the only available "encoded authentication" method on Windows CE 2.x devices, you can use Secure Sockets Layer (SSL) to encrypt the username and password.

Integrated

Integrated, or Windows, authentication requires the presence of a domain user account. Because you must have a domain user account, you can only use integrated authentication on an intranet. Furthermore, only Windows CE 3.x supports this method of authentication. Windows uses a hashing algorithm to encrypt the username and password across the connection.

SQL Server authentication methods

SQL Server consists of two types of authentication:

• Mixed Mode
• Windows authentication

Mixed Mode allows users to connect by using SQL Server authentication or Windows authentication. Windows authentication uses a domain account for access to SQL Server.

Mixed Mode

If your account is set up for a SQL Server login, you provide a login and password that is independent of your Windows login. Microsoft does not consider this method a secure login. One of the reasons this method is not secure is because there is no enforcement of password length. This method is also known as Standard Authentication.

Windows authentication

When you use Windows authentication, SQL Server maps your Windows login to a login and user account in SQL Server. Windows again uses a hashing algorithm to pass the username and password to the server.

Putting it all together

SQL Server CE, SQL Server 2005 Compact Edition, or SQL Server 2005 Mobile Edition connects to the remote SQL Server through IIS by using Replication or Remote Data Access (RDA). SQL Server CE, SQL Server 2005 Compact Edition, or SQL Server 2005 Mobile Edition implements ActiveX objects to pass the appropriate login and password. The various security scenarios that follow describe the uses of each security model.

Anonymous IIS and Standard Mode SQL Server

You should only use this method for testing purposes. This mode is not secure and relies only on SQL Server login for security.

Anonymous IIS and Windows Mode SQL Server

In this scenario, you need to set up a login and user account on SQL Server for the Anonymous IIS User account (typically IUSR_Machinename). You can then grant permissions for this user to access its appropriate database.

Basic IIS and Standard Mode SQL Server

Because Basic Authentication passes a username and password, you need to be sure that SSL is installed. You also need a valid login on SQL Server. You can pass this information through the SQL Server CE, SQL Server 2005 Compact Edition, or SQL Server 2005 Mobile Edition replication or RDA objects to connect to the remote SQL Server.

Basic IIS and Windows Mode SQL Server

If you are setup as a user on SQL Server, you can access SQL Server. SQL Server checks your username against its user list, checks your password, and then logs you in.

Note Basic authentication sends passwords across the network encoded in Base64. If you want to encrypt the user credentials, you can enable SSL for basic authentication. The username and password are transmitted after they are encrypted by the client's SSL support.

Integrated IIS and Standard Mode SQL Server

Sometimes an administrator may only want to administer one IIS server, but may also have other SQL Server databases. By using Integrated Authentication on the IIS server, the administrator can secure the SQL Server database separately from the IIS server.

Integrated IIS and Windows Mode SQL Server

This method is the most secure due to your use of Windows authentication throughout the connection process. You need to remember, however, that this scenario works only on an intranet.