Windows Integrated authentication connects the SQL Server database to create a more secure connection

server|window| Security | data | database

Summary: Today, write a Windows service program in C # that connects to a SQL Server 2000 database on a Windows 2003 server, using SQL Server Authentication. Common programs are found to be able to be linked, but Windows service programs are not. Later, I looked up the data and found the solution. Share to everyone.

Three-Step solution:

1, create a user on the server, configure the SQL Server database access rights.

2. Create an identical user on the client.

3, the service program with the newly created client user login.

Get!

Keywords: SSPI, integrated security, SQL Server and Windows, authentication, SQL Server does not exist, or access is denied.

Reference documentation: Windows Authentication and SQL Server

Http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/f_and_m/html/vxconWindowsAuthenticationSQLServer.asp

Visual Studio Example: Fitch and Mather 7.0

Windows Authentication and SQL Server

Fitch and Mather 7.0 accesses SQL Server through SQL Server authentication. Although it is very simple and provides an easy to understand example, it is not the safest method. Fitch and Mather 7.0 stores connection strings (including database credentials) in two locations: Web.config files and COM + Admin directories (in the Fmstocks7.gam.7 component's constructor string). By default, any user can read both locations, so it is easy to inadvertently disclose confidential information (credentials).

A better solution is to use Windows integrated security. First, you need the Windows identity that is recognized by both the Internet Information Services (IIS) server and the SQL Server database. There are two ways to choose:

· Domain account
This is the best approach if the server is part of a domain and the administrator can create domain accounts for the application.

· Local account with sync password
If the implementation of the network does not support domain accounts, you can safely access IIS and SQL Server databases by creating the same local account (using the same password) on both computers.

Select one of the above methods, and then create an account named Fmstocks_7 application (either on the domain or on both computers) and make it only belong to the Guest group. This allows the application to run with minimal privileges.

Then, change the connection string (in the web.config and COM + admin directories) and remove the explicit credentials so that you can use integrated security. For example, if the connection string is as follows:

Data Source=mydbserver; User Id=fitch and Mather 7.0 _login; password=*********;

Initial Catalog=fitch and Mather 7.0;

Then change it to:

Data source=mydbserver;integrated Security=sspi;

Initial Catalog=fitch and Mather 7.0;

This change means that you can access the SQL Server database using the identity used by the thread to run. By default, ASP. NET (Aspnet_wp.exe process) runs under the local ASPNET account, but the application code should run under the Fitch and Mather 7.0 application account. The advantage of running the application code under the Fitch and Mather 7.0 application account is that this is a Windows account, and you can grant it the appropriate SQL Server permissions.

Next, you need to associate the account with the Fitch and Mather 7.0 application.

Associate accounts with Fitch and Mather 7.0

1. Configure IIS.

A. On the Start menu, point to Programs, point to Administrative Tools, and then click Internet Services Manager.
The Internet Information Services window opens.

B. On the Tree tab, browse the directory tree to search the FMSTOCKS7 virtual directory. (The location of this virtual directory varies depending on the specific installation.) After you find it, right-click "FMStocks7" and select "Properties".
The FMStocks7 Properties dialog box appears.

C. In the FMStocks7 Properties dialog box, select the Directory Security tab. Under Anonymous access and authentication control, click the Edit button.
The Authentication Methods dialog box appears.

D. Under Anonymous access, click the Edit button.
The Anonymous User Account dialog box appears.

E. Clear "Allow IIS to control password". This is required because IIS stores the credentials and provides these credentials when authenticating against SQL Server.

F. Under User name and password, replace the anonymous user account with Fmstocks7_application and enter its password.

G. Finally, click "OK" three times to complete.

2. Enable impersonation in Web.config by adding the following line:

3. <system.web>

4. <identity impersonate= "true"/>

....

The final step is to configure SQL Server in the FMSTOCKS7 and Fmstocks7_gam databases to grant the appropriate permissions to the Fitch and Mather 7.0 application account (if the two databases are running on different computers, you need to Repeat this process on the machine):

Configuring SQL Server permissions in the FMSTOCKS7 and Fmstocks7_gam databases

1. Open SQL Server.

· On the Start menu, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
The SQL Server Enterprise Manager window opens.

2. Create a SQL login.

A. On the Tree tab, browse the directory tree to search the security folder under the server name, and then expand the folder.

B. Right-click the login folder and select New Login.
The SQL Server logon Properties-New Login dialog box appears.

C. In the Name box, enter "FMSTOCKS7 application".

D. Under authentication, locate the Domain box and select the local computer name (such as mydbserver) or your domain, depending on where you created the Fitch and Mather 7.0 application account.

E. Set security access to allow access.

F. Under default, set database to FMStocks7.

G. Select the Database Access tab and make sure that FMStocks7 and/or Fmstocsk7_gam are selected in their Allow fields.

H. under FMStocks7 and/or Fmstocks7_gam database roles, select Db_denydatareader and Db_denydatawriter permissions to prevent the account from accessing the table directly.

3. Delete Fmstocks7_login because it is no longer used.

. Still, on the Directory Tree tab in the SQL Server Enterprise Manager window, double-click Login under the Security folder.
Each login appears in the right pane of the SQL Server Enterprise Manager window.

A. Locate the Fmstocks7_login item and right-click the item and choose Delete to delete it.
The Fmstocks7_login dialog box appears. Select Yes to confirm the deletion.

4. Grant "execute" permission to all stored procedures.

. Still, on the Tree tab of the SQL Server Enterprise Manager window, browse the directory tree to search for the FMSTOCKS7 database that is under the name of the server in the database folder.

A. Locate and open stored procedures in the FMStocks database folder, and then perform the following actions on the non-system stored procedures:

Double-click the stored procedure (for example, the Account_Add item).
The Stored Procedure Properties dialog box appears.

Click the Permissions button.
The object Properties-fmstocks 7 dialog box appears.

In the Permissions table, locate the Exec column and select the appropriate box to grant EXEC permission to the FMSTOCKS7 application account.

To verify that SQL Server permissions are configured correctly, run the application and use SQL Analyzer to verify that the account you are using is the Fitch and Mather 7.0 application.

For each request, the following conditions will now occur:

1. An anonymous request is passed in, and IIS simulates the account you specified for the anonymous user (Fitch and Mather 7.0 application) and passes the request to aspnet_wp.exe. Note that although Fitch and Mather use Forms authentication, all requests are anonymous in IIS.

2. Because you have configured your application to use impersonation (by changing web.config), ASP.net emulates the IIS identity (the Fitch and Mather 7.0 application) in the current thread.

3. When an application opens a SQL connection, it executes the stored procedure using integrated Windows authentication under the 7.0 application login for Fitch and Mather.

Distributed scenarios

When running a distributed scenario, be aware of the following:

Remote BLL
All of the above changes to the WEB server need to be done in the application layer (where the BLL is running).

Remote GAM (. NET remoting)
All of the above changes to the WEB server also need to be done in the application layer (where the GAM is running).

Remote GAM (DCOM)
The above changes need to be done only on the WEB server. Also, on the computer that is running GAM (the computer where the GAM COM server application resides), complete the following actions:

Set up GAM

1. Open Component Services, and then modify the account for the FMStocks7.GAM COM + component.

A. On the Start menu, point to Programs, point to Administrative Tools, and then click Component Services.
The Component Services window opens.

B. On the Tree tab, browse the directory tree to search the "FMStocks7.GAM" folder under "-> My Computer"-> "COM + applications" in Component Services-> "Computer".

C. Right-click the FMStocks7.GAM item and select Properties.
The FMStocks7.GAM Properties dialog box appears.

D. Select the Identification tab, select this user, and then provide Windows credentials for the FMSTOCKS7 application in the user and Password boxes. Click OK to accept the changes.

2. Change the fmstocks7.gam.7 constructor string.

A. In the Component Services window, under the Components folder of the FMStocks7.GAM application (see step 1th above), right-click the "FMStocks7.GAM 7" component and select Properties.
The Fmstocks7.gam.7 Properties dialog box appears.

B. Select the Activation tab. Under object structure, locate the creator string and change the string to:
Provider=sqloledb;data source= mydbserver;integrated security=sspi;initial catalog=fitch and Mather 7.0;