{"id":72447,"date":"2017-09-06T17:22:06","date_gmt":"2017-09-06T17:22:06","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=72447"},"modified":"2021-06-03T16:47:00","modified_gmt":"2021-06-03T16:47:00","slug":"managing-password-applications-user","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/dotnet-development\/managing-password-applications-user\/","title":{"rendered":"Managing the Password of the Application’s User"},"content":{"rendered":"

When we use Windows Authentication, Active Directory enforces password policy for the users, including expiration policy. This was an unwelcome difference between window and SQL Logins until SQL Server 2012, when SQL Server started to enforce windows password policies to SQL Server logins by default.<\/p>\n

<\/p>\n

Policy settings for SQL Server Login<\/p>\n

However, most people will ask why this is necessary. Only a user that is connecting directly to SQL Server in SSMS<\/strong> is able to change his password when it expires and the majority of SQL Server users will connect through some application which, in turn, connects to SQL Server using a single login created for it.<\/p>\n

The application login is stored by the application in some kind of configuration file. In .NET<\/strong>, they are stored in the .config<\/strong> files with their passwords. In this case, why would you want to enforce domain password policy, if the application won’t be able to change the password anyway?<\/p>\n

The answer is, of course, that you wouldn\u2019t but what if the application could, in fact, change the password? Then an expiration policy makes sense. I will show in this article how we can achieve a security regimen where the application can conform to the password policy requirements automatically and no one, not even the developers, database or IT administrators, will know the password used by the application, thereby enhancing the security of our application environment.<\/p>\n

Development Environment for this article<\/h2>\n

I’m using SQL Server 2017 CTP 2.1<\/strong> and Visual Studio 2017<\/strong> to develop this solution, but I will use a library from SQL Server 2014<\/strong>, as I will explain later in the article.<\/p>\n

You can use different Visual Studio<\/strong> versions, but the solution as it stands will only work with Entity Framework 6.0<\/strong> and later.<\/p>\n

You will also need IIS (Internet Information Server)<\/strong> configured on the machine so you can simulate the deployment process.<\/p>\n

I will use the \u2018Northwnd\u2019<\/em> sample database, which you can download from here<\/a>. I will restore the \u2018Northwnd.bak\u2019<\/em> file twice, creating two databases: \u2018Northwnd\u2019<\/em> and \u2018NorthProduction\u2019<\/em>.<\/p>\n

We will start with an existing small solution with two projects. You can download the starting solution from this link: https:\/\/github.com\/DennesTorres\/ChangePassword\/tree\/master\/slCustomersStarting<\/a> . One of the projects is an MVC Web Application<\/strong> with a single controller and view. The controller accesses customers\u2019 information in the \u2018Northwnd\u2019<\/em> database and exposes this information to the view. The database access is executed by the second project, a class library with the Entity Framework context.<\/p>\n

The use of the password in the development process<\/h2>\n

How can we manage our development and deployment process without knowing the password for the database server?<\/p>\n

Let\u2019s suppose we have three environments: Development, QA\/Test and Production. Developers need access to the development environment, so we can\u2019t hide the password in this environment. QA\/Test and Production, however, can have a stricter access control.<\/p>\n

When the application is published from development to QA\/Test, and thence to Production, the connection string is changed. Some people still do it manually, however, we have web.config<\/strong> transformations to do it for us during the publishing process.<\/p>\n

The secret here is in the database layer: Each time a new deployment is completed, the DBA needs to reset the password for the database login to a starting password known by the deployment process and configured in the web.config<\/strong>.<\/p>\n

The login needs to be configured with \u2018User must change password at next login\u2019<\/strong> configuration, so the application will change the password during the first login after the deployment and no one but the application will know the new password.<\/p>\n

However, after the password change, the password will be inside the web.config<\/strong>, won\u2019t it? Yes, it will, but the IT team can encrypt the web.config <\/strong>during the deployment process. It\u2019s already difficult to have access to the web.config<\/strong>, only some members of the IT team have this kind of access, with the automatic encryption the access will be even more restrict and we will achieve the objective: No one but the application will know the password.<\/p>\n

In this article, I will build the application and test it as it was in the QA\/Test environment and do some steps of the deployment process to the Production environment.<\/p>\n

The Solution<\/h2>\n

Our solution needs to change the login password every time it expires, or when the login is marked with the ‘User must change password at next logon’ <\/strong>option. We will need to break the solution in parts to achieve a better code organization. These will be the parts of our solution:<\/p>\n