We have a customer who uses Form based authentication in his ASP.NET application. But he also wants to use FileStream feature. Authentication becomes a challenge. In order for FileStream to work, sql connection needs to use ingetrated security (not SQL Server based login). Here is the post.
When you use Anonymous Access and Form based authentication, windows users cannot be impersonated. Therefore, you can't take the windows user from web client to authenticate to SQL Server. Most of the time, a developer just use SQL login to access SQL Server database. But this is not an option for File Stream access. Fortunately, there is an easy solution to this. The solution is that you use IIS process level identity to logon to SQL Server using Integrated Security.
Even with IIS's Anonymous Access, IIS still has a process level identity at Windows level. You can take advantage of this fact to allow this process account access to SQL Server using Windows authentication.
Here is a summary:
- Configure IIS to use Anonymous access
- Configure your application pool's Identity to use "Network Service"
- Grant the IIS Machine account access to SQL Server
- In your SQL Server connection string, use Integrated Security
Step by Step Instruction (using Windows 2003 Server / IIS 5.0)
- Web Server Machine Name: MachineA on Domain1. This machine has a domain account (automatically created when joining the domain) called Domain1\MachineA$.
- SQL Server Machine Name: MachineB on Domain1. This machine has a domain account (automatically created when joining the domain) called Domain1\MachineB$.
- FileStream Database Name: Archive
- It is assumed that your IIS 6.0 is using default native mode (not IIS 5.0 isolation mode)
- Ensure IIS is using "Network service" as identity. This is the default but can be configured as following
Grant the IIS machine account to access SQL Server
- Under IIS Manager's "Application pool", right click on the ASP.NET application pool you have chosen for your application
- choose property
- go to "identify" tab, ensure you select "Predefined" and choose "Network Service". if you don't do this, you will receive error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
Configure web.config as the following
- On SQL Server grant login for domain1\machineA$ (this is the machine account in your domain). You can also use TSQL to do this: sp_grantlogin 'domain1\machineA$ '
- Go to database Archive and grant necessary permissions to access that database.
Configure your web site to allow "Anonymous Access"
Configure all SQL connection string to use windows authentication
- <authentication mode="Forms" />
- <identity impersonate="false"/> (note default is false. You cannot set this to be true if you allow Anonymous access to IIS. Otherwise, you receive error "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."
The above steps will allow your application to connect to SQL Server using Windows authentication under the account of Domain1\MachineA$
- In your FileStream application, ensure all SQL Connection use "Integrated Security". Example: SqlConnection sqlConnection = new SqlConnection( "Integrated Security=true;server=MachineB");
Note: you can also customize IIS process account without having to use "Network Service" (or machine account). Please consult IIS documentation on how to achieve this.
Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support