Microsoft KB Archive/314783

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

Article ID: 314783

Article Last Modified on 12/3/2007

-

APPLIES TO


 * Microsoft Internet Information Services 5.0
 * Microsoft Encarta Interactive World Atlas 2001
 * Microsoft SQL Server 2000 Windows CE Edition 1.1
 * Microsoft Internet Information Services 6.0
 * Microsoft SQL Server 2000 Windows CE Edition 2.0
 * Microsoft SQL Server 2005 Compact Edition

-



This article was previously published under Q314783





We strongly recommend that all users upgrade to Microsoft Internet Information Services (IIS) version 6.0 running on Microsoft Windows Server 2003. IIS 6.0 significantly increases Web infrastructure security. For more information about IIS security-related topics, visit the following Microsoft Web site:

http://www.microsoft.com/technet/security/prodtech/IIS.mspx



SUMMARY
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 or SQL Server 2005 Compact 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:

http://msdn2.microsoft.com/en-us/library/aa224604(SQL.80).aspx

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 or SQL Server 2005 Compact 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 &quot;encoded authentication&quot; 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 or SQL Server 2005 Compact Edition connects to the remote SQL Server through IIS by using Replication or Remote Data Access (RDA). SQL Server CE or SQL Server 2005 Compact 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 or SQL Server 2005 Compact 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.

Additional query words: iis6 iis 6.0

Keywords: kbhowtomaster KB314783

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.