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 Domain1MachineA$.
- SQL Server Machine Name: MachineB on Domain1. This machine has a domain account (automatically created when joining the domain) called Domain1MachineB$.
- 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
- 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 AUTHORITYANONYMOUS LOGON'
- On SQL Server grant login for domain1machineA$ (this is the machine account in your domain). You can also use TSQL to do this: sp_grantlogin 'domain1machineA$ '
- Go to database Archive and grant necessary permissions to access that database.
- <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."
- 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