The problem is frequently caused by mismatched security settings, and/or connecting to the wrong Sql instance. Here is a sample connection string…
Data Source=.\sqlexpress2012;Initial Catalog=DotNetNuke_Community_07.01.00;Integrated Security=True
Here is what the parts mean…
|
Part
|
Meaning
|
|
Data Source=.\sqlexpress2012
|
The ‘.’ Indicates that the desired instance of Sql Server is on the local machine. If it is not on the local machine then use the name or IP address of the machine it is on.
The ‘sqlexpress2012’ indicates the ‘instance name’ of the Sql Server instance desired. Sql Server supports installing multiple times to the same server. The different instances are often different versions of Sql Server.
For a default install of ‘full’ SqlServer there is often no instance name - use 'Data Source=.'. For a default install of Sql Server Express the instance name is often SqlExpress.
|
|
Initial Catalog=DotNetNuke_Community_07.01.00
|
Specifies the name of the database you wish to connect to. Database names are established when the database is created and are unique within an instance.
|
|
Integrated Security=True
|
Sql Server has two ways of authenticating connections. ‘Integrated Security’ means that Sql Server must allow the account running the web site access to the desired database.
|
What account runs the web site?
In Windows; all executing code belongs to an account. In the case of a web site the account is established by the Application Pool the site is configured to use.
To set or determine the Application Pool ; select the site in IIS Manager and look in ‘Basic Settings’
To find the actual account right click the Application Pool and look in Advanced Settings. 'Application Pool Identity' is the default setting.
In this, default, case it is the application pool’s identity that needs to be configured into Sql Server. Sql Server refers to accounts as Logins. The application pool's identity means the Windows account used by the application pool. We need a Login in Sql Server linked to the Windows account used by the application pool. The application pool’s identity will be of the form [IIS APPPOOL\ASP.NET v4.0]. The square brackets enclosing the actual name are a convention permitting the name to contain special characters.
The Gui in Sql Server Management Studio does not make it straightforward to create the login because the application pool accounts do not show up in it. Use this TSQL…
CREATE LOGIN [IIS APPPOOL\ASP.NET v4.0] FROM WINDOWS;
…which will create a SQL Server Login to work with the application pool’s identity.
If you really want to use the Gui see this StackOverflow answer. http://stackoverflow.com/questions/1933134/add-iis-7-apppool-identities-as-sql-server-logons
Once the login exists then this TSQL will allow it access to your database.
USE [DotNetNuke_Community_07.01.00]
CREATE USER [IIS APPPOOL\ASP.NET v4.0] FOR LOGIN [IIS APPPOOL\ASP.NET v4.0]
GO
EXEC sp_addrolemember N'db_datareader', N'IIS APPPOOL\ASP.NET v4.0'
GO
EXEC sp_addrolemember N'db_datawriter', N'IIS APPPOOL\ASP.NET v4.0'
GO
EXEC sp_addrolemember N'db_owner', N'IIS APPPOOL\ASP.NET v4.0'
GO
The use statement identifies the database SQL statements apply to.
The CREATE USER statement gives a SQL Server login access to a specific database
The system stored procedure sp_addrolemember grants a database user membership of a specific role in the database. Roles control what individual users are allowed to do.