MS SQL server has many versions available, sometimes it creates more confusion than help when an asp.net app works with visual studio but not after it is deployed to iis.
SQL Express is a simplified version of SQL server. Note it requires a different installation to install.
SQL server is the easiest one to understand, it has a single instance to be accessed.
SQL Express is a simplified version of SQL server. Note it requires a different installation to install.
SQL Express User Instance and LocalDB: These are more for easy integration with Visual Studio, so the database are private to the application. But it causes permission issue if you need to work with iis deployment or developing environment switch. If you are not quite familiar with them like me, it makes sense to avoid them in your project, as the effort to fix the configuration or permission issues may take longer than using a shared sqlExpress database instance. At least, it is easy to debug and understand if anything goes wrong. Simply is always better.
To debug asp.net project after iis deplyment, attach visual studio to w3wp.exe.
If your asp.net application does not work after deploying to iis, the following issues may cause it:
1. The permission issue from SQL Express User Instance and LocalDB, change the database connection to SQL server or SQLExpress shared instance.
2. After that, for "opening database errors" when connecting to database, it may happen because the SQL server or SQLExpress instance is missing, first check whether the instance is available, open "SQL server configuration manager" under sql server 2012's "Configuration tools" application, select SQL Server Service node, and see whether the shared instance service is there.
To debug asp.net project after iis deplyment, attach visual studio to w3wp.exe.
If your asp.net application does not work after deploying to iis, the following issues may cause it:
1. The permission issue from SQL Express User Instance and LocalDB, change the database connection to SQL server or SQLExpress shared instance.
2. After that, for "opening database errors" when connecting to database, it may happen because the SQL server or SQLExpress instance is missing, first check whether the instance is available, open "SQL server configuration manager" under sql server 2012's "Configuration tools" application, select SQL Server Service node, and see whether the shared instance service is there.
3. For database permission issue, you may want to change the logon user for SQL server or SQLExpress service to local system to avoid any permission issue.
4. For Asp.net application pool permission issue, open iis manager, change all application pool's advanced settings' identity to local system.
5. if an error happen say "'NT AUTHORITY\SYSTEM" account cannot access the database, then either use database authentication instead of Windows authentication in connection string or open database manager studio, and make sure 'NT AUTHORITY\SYSTEM' exists under security/logins node. then change the "Default database" to the application's database, and also under "User Mapping", select the application database, and "public" adn "db_owner" role.
4. For Asp.net application pool permission issue, open iis manager, change all application pool's advanced settings' identity to local system.
5. if an error happen say "'NT AUTHORITY\SYSTEM" account cannot access the database, then either use database authentication instead of Windows authentication in connection string or open database manager studio, and make sure 'NT AUTHORITY\SYSTEM' exists under security/logins node. then change the "Default database" to the application's database, and also under "User Mapping", select the application database, and "public" adn "db_owner" role.
wonderful! thanks.
ReplyDelete