SQL Authentication and Authorization in Software Test Automation, Part I

In many software test automation scenarios, you will store test case data and test results in a SQL database. It is not uncommon for beginners to be confused about several issues related to SQL Server authentication and authorization. When you install the software for a SQL Server machines you can specify either Windows Authentication mode or Mixed mode. In Windows Authentication mode, SQL Server uses your Windows user credentials to establish who you are (where "you" can be mapped to an actual person or just a virtual user account), which then establishes your authorization permissions. In theory this is efficient, secure, and tidy. However, in practice, because the Windows security model is so complex, when writing test automation connection issues often arise. Furthermore, if you are operating in a non-homogeneous environment (with non-Microsoft servers and technologies), in many cases pure Windows Authentication mode just doesn’t work. So, most of my test automation colleagues prefer setting up SQL Server with Mixed Mode Authentication. For example, suppose your test harness needs to connect to a database dbTestCases. You’ve set up Mixed Mode Authentication and created an account named ‘harness’ which has password ‘secret’. To connect using Windows Authentication, your code will resemble:


string sConn = "Server=(local);Database=dbTestCases;Trusted_Conection=yes";


And to connect using SQL (Mixed) Authentication, your code would resemble:


string sConn = "Server=(local);Database=dbTestCases;UID=harness;PWD=secret";


You can see there’s not much difference in the code but in my experience, the Mixed Mode authentication version is less troublesome.

This entry was posted in Software Test Automation. Bookmark the permalink.