This topic describes how Microsoft SQL Server Compact 3.5 works with:
SQL Server Authentication
SQL Server Authorization
SQL Server Authentication
SQL Server supports two forms of authentication:
Windows Authentication
SQL Server Authentication
Windows Authentication
When Windows Authentication is used to connect to SQL Server, Microsoft Windows is completely responsible for authenticating the client. In this case, the client is identified by its Windows user account. With SQL Server Compact 3.5 replication or remote data access (RDA), the SQL Server Compact 3.5 Server Agent acts as the database client, running under the identity of a Windows user account:
When IIS is configured to use Anonymous access, the SQL Server Compact 3.5 Server Agent runs under the identity of the Internet Guest Account. By default, this is IUSR_computername. If you configure another Windows user account as the IIS anonymous user account, the SQL Server Compact 3.5 Server Agent runs under the identity of that account.
When IIS is configured to use Basic authentication, the SQL Server Compact 3.5 Server Agent runs under the identity of the Windows user account for which the client supplied the Internet user name and password.
When IIS is configured to use Integrated Windows Authentication, the SQL Server Compact 3.5 Server Agent runs under the identity of the client.
SQL Server Authentication
When SQL Server Authentication is used, SQL Server authenticates the client by comparing the client-supplied user name and password to the list of valid user names and passwords maintained within SQL Server. In this case, the application must supply the appropriate SQL Server logon and password when connecting to SQL Server using replication or remote data access. The SQL Server Compact 3.5 Client Agent conveys the logon and password to the SQL Server Compact 3.5 Server Agent. The SQL Server Compact 3.5 Server Agent invokes SQL Server by using the logon and password specified by the client.
SQL Server Authorization
When a client is successfully authenticated, the user or group name of the client is mapped to a SQL Server user account.
The client must have a user account in each database that it wants to access. The user account is used to control access to the tables, views, stored procedures, and so on, in that database. The activities that a client can perform are controlled by the permissions applied to the user account through which the client gained access to the database.
For RDA only: You can precisely control the database operations that RDA clients can perform by the permissions you grant them. For more information, see Granting Access to a SQL Server Database.
For replication only: When you create a publication, SQL Server creates a publication access list (PAL) for the publication. You must update the PAL to grant access to the publication.
When you create a publication, the location of the snapshot folder is specified. The snapshot folder is used when a subscription is first created or reinitialized. If the computer that is running SQL Server is located on an NTFS file system, then the appropriate NTFS permissions must be specified. For more information, see Configuring the Snapshot Folder.
When you create a publication, you can use the Check Permissions option to provide an enhanced level of security to the publication. The Check Permissions option ensures that the Merge Agent has the authority to upload data changes to a Publisher. When you specify this option, SQL Server verifies that the Merge Agent logon has the permissions to perform INSERT, UPDATE, and DELETE statements on the publication database.