We all know it: If we have problems accessing a database, we can assign the db_owner role to the account that accesses the database - or even worse, we assign the sysadmin role for the whole server to this account. And we have that presentiment that we could open some security holes by doing so. So what is reasonable?

Note: None of this relieves us of the need to regularly back up the database!

Which permissions does this account need?

This opens up the first problem: of course this account needs more permissions while installing DNN or any extension than for the normal operation. In this case we have to increase these permissions temporarily and set them back after we're done - or use the upgradeConnectionString. I will discuss this at the end of this article.

During the normal operation, we need to:

• Read data
• Add, update and delete data
• Execute stored procedures and functions

Luckily we have predefined roles for the first two of these roles (db_datareader and db_datawriter), but executing stored procedures and functions are not included in those. The solution is creating an additional role, just by running this script:

USE [DNN]
GO

-- Create the role db_executor
CREATE ROLE db_executor
GO

-- Grant execution permission to this role
GRANT EXECUTE TO db_executor
GO

Note: of course you have to change the name of your database in the first line of this script.

A short detour: What kind of SQL Server login?

Which is the best practice to create the SQL Server user for DNN? There are three scenarios:

1. IIS and SQL Server on the same machine: Use the application pool identity, and Windows authentication, eg. IIS AppPool\DNNAppPool
2. IIS and SQL Server on different machines within the same Windows domain: Use the name of the machine (followed by a Dollar-sign), on which IIS runs, and Windows Authentication, eg. MYDOMAIN\DnnWebServer$
3. IIS and SQL Server on different machines, and not in the same Windows domain, or no domain at all: Use SQL Server authentication.

In this example, I use two different machines whicht are not in a domain - and the SQL Server is not installed on Windows, but on Ubuntu Server, a Linux distribution. How to do that? This is another story (and will maybe be another blog article...).

Assign the execution permission to the login

Finally we have to assign the correct roles to the user from the connection string. We do this in SQL Server Management Studio, navigate to Security :: Logins, right click the login and select Properties. Select User Mappings on the left side, and on the right side check the database, uncheck db_owner and check db_datareader, db_datawriter and db_executor.

That's it - more or less. But what happens when we install an extension or an update? With this configuration, the accout is not allowed to create, modify or delete tables, indexes or other database objects. We get an error message like

or

No problem - everything you have to do is assigning the db_owner role to this account during the update, and remove it when you're done.

There is another way to do this, which is one of the "hidden gems" in DNN and has been introduced long ago, although rarely documented: the "upgradeConnectionString". You need an SQL-Server-Login which has db_owner permissions on the database for realizing this. To create such a login it is necessary to allow SQL Server Logins. In SQL Server Management Studio, connect to the database server, and right click it's name and select Properties. Select the Security page, and check if "SQL Server and Windows Authentication mode" is selected in the Server authentication options.
In SQL Server for Linux, this option is only available if you configure the Active Directory authentication with SQL Server on Linux (which means: Only SQL Server logins are available, so you can go on and implement this).

Note: If you have to change this option, you have to restart SQL Server to take the setting into effect.

Next step is to create the SQL Server login. In SSMS, open Security and right click on Logins. Select "New Login...", and fill the data in the General page as follows:

1. Login Name: Enter a user name, e.g. DNNUpdateAdmin
2. Select the SQL Server Authentication option
3. Password/Confirm password: Enter a secure password
4. Deactivate Enforce password policy - you don't want to change this password from time to time, therefore it is important to choose a secure password
5. If you want you might change the Default Database to the DNN database in use (not necessary).

Go to User Mapping, and check the DNN database and the db_owner role.

Click OK to create the login.

Finally, you have to enter the connection string for updates in the web.config file. Open the file with a text editor, search for "upgradeConnectionString", and enter something like:

<data defaultProvider="SqlDataProvider">
   <providers>
   <clear />
      <add name="SqlDataProvider"
         type="DotNetNuke.Data.SqlDataProvider, DotNetNuke"
         connectionStringName="SiteSqlServer"
         upgradeConnectionString="Persist Security Info=False;User ID=DNNAdmin;Password=MySecret#123;Initial Catalog=DNN;Data Source=UBUNTU"
         providerPath="~\Providers\DataProviders\SqlDataProvider\"
         objectQualifier=""
         databaseOwner="dbo" />
   </providers>
</data>

Now, installing updates and extensions work normally, without the need to change permissions before and after the instalation.

I personally do not like this method, even if it is more conveniant. The reason is that a user name and a password have to be stored in clear text, and this login has the highest permission in the database. If someone is able to steal your web.config, he is also able to steal your database, but this is also the case for the scenario using an SQL Server login up above.