KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub

Q314783: HOW TO: Plan SQL Server CE Security for Mobile Devices in IIS

Article: Q314783
Product(s): Internet Information Server
Version(s): 1.0,1.1,5.0
Operating System(s): 
Keyword(s): kbAudITPro kbHOWTOmaster
Last Modified: 24-JUN-2002

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Internet Information Services version 5.0 
- Microsoft SQL Server 2000 Windows CE Edition, versions 1.0, 1.1 
-------------------------------------------------------------------------------


IN THIS TASK
------------

- SUMMARY

   - Choosing an Authentication Method for IIS

   - Anonymous
- Basic
- Integrated

- SQL Server Authentication Methods

   - Mixed Mode

- Windows Authentication

- Putting it All Together

   - Anonymous IIS and Standard Mode SQL Server
- Anonymous IIS and Windows Mode SQL Server

- Basic IIS and Standard Mode SQL Server
- Basic IIS and Windows Mode SQL Server
- Integrated IIS and Standard Mode SQL Server
- Integrated IIS and Windows Mode SQL Server

SUMMARY
=======

This guide assists you in developing your Microsoft Internet Information Server
(IIS) and 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 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:

  Security Models and Scenarios for SQL Server CE
  (http://www.microsoft.com/sql/techinfo/administration/2000/securitymodels.asp)

Choosing an IIS 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 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

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 connects to the remote SQL Server through IIS by using Replication
or Remote Data Access (RDA). SQL Server CE 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 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.

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:

======================================================================
Keywords          : kbAudITPro kbHOWTOmaster 
Technology        : kbSQLServSearch kbiisSearch kbAudDeveloper kbiis500 kbSQLServ2000Search kbSqlServ2000CEE kbSqlServ2000CEE100 kbSqlServ2000CEE110
Version           : :1.0,1.1,5.0
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.