Connecting to SQL Server Using Integrated Security from ASP.NET
Problem
You want to coordinate Windows security accounts between an ASP.NET application and SQL Server.
Solution
Connect to SQL Server from ASP.NET using Windows Authentication in SQL Server.
Discussion
Connecting to a SQL Server database provides two different authentication modes:
Windows Authentication
Uses the current security identity from the Windows NT or Windows 2000 user account to provide authentication information. It does not expose the user ID and password and is the recommended method for authenticating a connection.
SQL Server Authentication
Uses a SQL Server login account providing a user ID and password.
Integrated security requires that the SQL Server is running on the same computer as IIS and that all application users are on the same domain so that their credentials are available to IIS. The following areas of the application need to be configured:
- Configure the ASP.NET application so that Integrated Windows Authentication is enabled and Anonymous Access is disabled.
- The web.config file establishes the authentication mode that the application uses and that the application will run as or impersonate the user. Add the following elements to the web.config file:
- The connection string must contain attributes that tell the SQL Server that integrated security is used. Use the Integrated Security=SSPI attribute-and-value pair instead of the User ID and Password attributes in the connection string. The older attribute-and-value pair Trusted_Connection=Yes is also supported.
- Add users and groups from the domain and set their access permissions as required.
By default, ASP.NET applications run in the context of a local user ASPNET on IIS. The account has limited permissions and is local to the IIS computer and therefore not recognized as a user on remote computers. To overcome this limitation when SQL Server is not on the same computer as IIS, run the web application in the context of a domain user recognized on both IIS and SQL Server computers.
In addition to the areas identified where IIS and SQL Server are on the same computer, the following additional items must be configured if the SQL Server is on a different computer:
- Ensure that the mapped domain user has required privileges to run the web application.
- Configure the web application to impersonate the domain user. Add the following elements to the web.config file for the web application:
Connecting to Data
- Connecting to an ODBC Data Source
- Connecting to a Microsoft Excel Workbook
- Connecting to a Password-Protected Access Database
- Connecting to a Secured Access Database
- Connecting to an Access Databasefrom ASP.NET
- Using an IP Address to Connect to SQL Server
- Connecting to a Named Instance of SQL Server or Microsoft Data Engine (MSDE)
- Connecting to SQL Server Using Integrated Security from ASP.NET
- Connecting to an Oracle Database
- Connecting to Exchange or Outlook
- Writing Database-Independent Code
- Storing Connection Strings
- Using the Data Link Properties Dialog Box
- Monitoring Connections
- Taking Advantage of Connection Pooling
- Setting Connection Pooling Options
- Using Transactions with Pooled Connections
- Changing the Database for an Open Connection
- Connecting to a Text File
Retrieving and Managing Data
- Retrieving and Managing Data
- Retrieving Hierarchical Data into a DataSet
- Building a DataSet Programmatically
- Creating a Strongly Typed DataSet
- Processing a Batch SQL Statement
- Using a Web Service as a Data Source
- Accessing Deleted Rows in a DataTable
- Counting Records in a DataReader
- Mapping .NET Data Provider Data Types to .NET Framework Data Types
- Returning an Output Parameter Using a DataReader
- Raising and Handling Stored Procedure Errors
- Testing for No Records
- Retrieving Stored Procedure Return Values Using a DataReader
- Executing SQL Server User-Defined Scalar Functions
- Passing Null Values to Parameters
- Retrieving Update Errors
- Mapping Table and Column Names Between the Data Source and DataSet
- Displaying Columns from a Related DataTable
- Controlling the Names Used in a Strongly Typed DataSet
- Replacing Null Values in a Strongly Typed DataSet
- Retrieving Data from an Oracle Package
- Using Parameterized SQL Statements
- Querying Data Asynchronously with Message Queuing
Searching and Analyzing Data
- Searching and Analyzing Data
- Filtering and Sorting Data
- Using Expression Columns to Display Calculated Values
- Determining the Differences in Data Between Two DataSet Objects
- Navigating Between Parent and Child Records Using a DataRelation
- Localizing Client-Side Data in a Web Forms Application
- Combining Data in Tables from Heterogeneous Data Sources
- Using Expression Columns to Display Aggregate Values
- Finding Rows in a DataTable
- Finding Rows in a DataView
- Selecting the Top n Rows in a DataTable
- Getting Typed DataRows from DataViews
- Filtering for Null Values
- Executing Queries That Use COMPUTE BY
- Using the Shape Language to Retrieve Hierarchical Data
Adding and Modifying Data
- Adding and Modifying Data
- Using Auto-Incrementing Columns Without Causing Conflicts
- Getting an Identity Column Value from SQL Server
- Getting an AutoNumber Value from Microsoft Access
- Getting a Sequence Value from Oracle
- Adding Parent/Child Rows with Auto-Incrementing Keys
- Adding Records with a GUID Primary Key
- Updating a Data Source with Data from a Different Data Source
- Updating a Primary Key Value
- Getting Stored Procedure Parameter Information at Runtime
- Updating a DataSet with a Many-to-Many Relationship
- Updating Server Data Using a Web Service
- Updating Server Data Using .NET Remoting
- Updating Data Asynchronously Using Message Queuing
- Overcoming Keyword Conflicts When Using CommandBuilders
Copying and Transferring Data
- Copying and Transferring Data
- Copying Rows from One DataTable to Another
- Copying Tables from One DataSet to Another
- Converting a DataReader to a DataSet
- Serializing Data
- Deserializing Data
- Merging Data
- Transmitting a DataSet Securely
- Transferring Login Credentials Securely
- Loading an ADO Recordset into a DataSet
- Converting a DataSet to an ADO Recordset
- Exporting the Results of a Query as a String
- Exporting the Results of a Query to an Array
Maintaining Database Integrity
- Maintaining Database Integrity
- Creating a Class That Participates in an Automatic Transaction
- Using Manual Transactions
- Nesting Manual Transactions with the SQL Server .NET Data Provider
- Using ADO.NET and SQL Server DBMS Transactions Together
- Using a Transaction with a DataAdapter
- Avoiding Referential Integrity Problems When Updating the Data Source
- Enforcing Business Rules with Column Expressions
- Creating Constraints, PrimaryKeys, Relationships Based on Multiple Columns
- Retrieving Constraints from a SQL Server Database
- Checking for Concurrency Violations
- Resolving Data Conflicts
- Using Transaction Isolation Levels to Protect Data
- Implementing Pessimistic Concurrency Without Using Database Locks
- Specifying Locking Hints in a SQL Server Database
Binding Data to .NET User Interfaces
- Binding Data to .NET User Interfaces
- Binding Simple Data to Web Forms Controls
- Binding Complex Data to Web Forms Controls
- Binding Data to a Web Forms DataList
- Binding Data to a Web Forms DataGrid
- Editing and Updating Data in a Web Forms DataGrid
- Synchronizing Master-Detail Web Forms DataGrids
- Displaying an Image from a Database in a Web Forms Control
- Displaying an Image from a Database in a Windows Forms Control
- Binding a Group of Radio Buttons in a Windows Form
- Creating Custom Columns in a Windows Forms DataGrid
- Populating a Windows Forms ComboBox
- Binding a Windows DataGrid to Master-Detail Data
- Loading a Windows PictureBox with Images Stored by Access as OLE Objects
- Using a DataView to Control Edits, Deletions, or Additions in Windows Forms
- Adding Search Capabilities to Windows Forms
- Dynamically Creating Crystal Reports
- Using ADO.NET Design-Time Features in Classes Without a GUI
Working with XML
- Working with XML
- Using XSD Schema Files to Load and Save a DataSet Structure
- Saving and Loading a DataSet from XML
- Synchronizing a DataSet with an XML Document
- . Storing XML to a Database Field
- Reading XML Data Directly from SQL Server
- Using XPath to Query Data in a DataSet
- Transforming a DataSet Using XSLT
- Creating an XML File That Shows Changes Made to a DataSet
- Formatting Column Values When Outputting Data as XML
- Filling a DataSet Using an XML Template Query
- Using a Single Stored Procedure to Update Multiple Changes to a SQL Server Database
Optimizing .NET Data Access
- Optimizing .NET Data Access
- Filling a DataSet Asynchronously
- Canceling an Asynchronous Query
- Caching Data
- Improving Paging Performance
- Performing a Bulk Insert with SQL Server
- Improving DataReader Performance with Typed Accessors
- Improving DataReader Performance with Column Ordinals
- Debugging a SQL Server Stored Procedure
- Improving Performance While Filling a DataSet
- Retrieving a Single Value from a Query
- Reading and Writing Binary Data with SQL Server
- Reading and Writing Binary Data with Oracle
- Performing Batch Updates with a DataAdapter
- Refreshing a DataSet Automatically Using Extended Properties
Enumerating and Maintaining Database Objects
- Enumerating and Maintaining Database Objects
- Listing SQL Servers
- Retrieving Database Schema Information from SQL Server
- Retrieving Column Default Values from SQL Server
- Determining the Length of Columns in a SQL Server Table
- Counting Records
- Creating a New Access Database
- Creating a New SQL Server Database
- Adding Tables to a Database
- Getting a SQL Server Query Plan
- Compacting an Access Database
- Creating DataSet Relationships from SQL Server Relationships
- Getting SQL Server Column Metadata Without Returning Data
- Listing Installed OLE DB Providers
- Listing Tables in an Access Database
- Creating a Table in the Database from a DataTable Schema
- Listing Installed ODBC Drivers
Appendix A. Converting from C# to VB Syntax
ADO.NET 3.5 Cookbook (Cookbooks (OReilly))
ISBN: 0596101406
EAN: 2147483647
EAN: 2147483647
Year: 2002
Pages: 222
Pages: 222
Authors: Bill Hamilton
