{"id":89986,"date":"2021-02-22T15:41:26","date_gmt":"2021-02-22T15:41:26","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=89986"},"modified":"2022-04-24T21:25:44","modified_gmt":"2022-04-24T21:25:44","slug":"sql-server-authentication-methods","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/sql-server-authentication-methods\/","title":{"rendered":"SQL Server authentication methods, logins, and database users"},"content":{"rendered":"

Setting up and managing SQL Server security is an important part of building and maintaining your SQL Server environment. SQL Server security is a vast topic that cannot be covered in a single article. This article starts with a few foundation topics of SQL Server security: SQL Server Authentication methods, logins and database users.<\/p>\n

Authentication methods supported<\/h2>\n

There are two different authentication methods for connecting to SQL Server: Windows and SQL Server.<\/p>\n

Windows authentication requires a user to first authenticate to Windows with their login and password. Once a user has been authenticated to Windows, they can then connect to SQL Server using Windows authentication. That is, provided their Windows account has been granted access to SQL Server via a login (more on logins later). Windows authentication is tightly coupled with Windows Security and is also known as Integrated Security<\/em>. Windows authentication works great when a person is part of a Windows domain.<\/p>\n

But there are times when people can\u2019t connect to Windows; this is where SQL authentication comes in. SQL Authentication is less secure than Windows Authentication. In order to connect to SQL Server using SQL authentication, a person needs to provide a login and password when they connect. The password for a SQL Authenticated login is stored in the master database. Because the password is stored in a SQL database, it is more easily hacked. It can also be backed up and restored with a database backup which is why it is less secure than using Windows authentication.<\/p>\n

Windows Authentication is the default authentication mode when installing a SQL Server instance. In order to support SQL authentication, you need to configure SQL Server to support mixed mode<\/em> authentication. When mixed mode is used, both Windows and SQL authentication can be used to connect to SQL Server. If SQL Server is not set up to support mixed mode, then only Windows accounts can connect to SQL Server.<\/p>\n

Because SQL authentication is less secure than Windows Authentication, it is recommended that you only set up your SQL Server instance to support mixed mode if you have a requirement to support users or applications that can\u2019t connect to Windows. Even though Windows Authentication is more secure and the recommended practice for connecting to SQL Server, many custom application software vendors still don\u2019t support connecting via Windows authentication.<\/p>\n

Setting up SQL Server to support different authentication modes<\/h2>\n

When installing SQL Server, you have the choice to support only Windows authentication or both authentication methods, which is known as mixed mode. During the installation process, you decide whether or not to use mixed mode when defining the database engine configuration, as shown in Figure 1.<\/p>\n

<\/p>\n

Figure 1: Selecting Authentication Methods<\/strong><\/p>\n

Windows Authentication is selected as the default shown with the red arrow in Figure 1. If you need to support both Windows and SQL Server authentication, then you would select the \u201cMixed Mode\u201d radio button. Upon clicking this button, the SA account password boxes would become enabled, and you would need to specify a password for the SA account. When only Windows authentication is selected, the SA account is disabled. To secure the SA account when you are using mixed mode you can disable the SA account after it is enabled.<\/p>\n

How to determine which authentication methods are supported<\/h2>\n

You can check to see which authentication method is configured in several ways. One of those ways is to use SQL Server Management Studio (SSMS). To use SSMS, first right click on the Instance name and select the Properties option. When I do that on my instance, the properties page in Figure 2 is displayed.<\/p>\n

<\/p>\n

Figure 2: Determining Authentication Mode<\/strong><\/p>\n

Figure 2 shows that my instance supports mixed mode authentication because the radio button next to the red arrow is enabled.<\/p>\n

Another method to check which authentication modes are set up is to use TSQL code. The code in Listing 1 displays the Authentication mode setup.<\/p>\n

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   \r\n          WHEN 1 THEN 'Windows Authentication Only'   \r\n          WHEN 0 THEN 'Windows and SQL Server Authentication'  \r\n       END as [Authentication Mode];<\/pre>\n
Listing 1: Displaying Authentication mode<\/strong><\/p>\n
Changing authentication methods After SQL Server is installed<\/h2>\n
There are times when you might want to change the authentication settings for a SQL Server instance. This might occur if you used the default settings during installation to support Windows authentication only and later acquired some software that can only connect using SQL Server authentication. Or possibly you want to make your instance more secure by removing support for SQL Server authentication. The authentication options can be easily changed using the properties page in SSMS shown in Figure 2.<\/p>\n
If I wanted to change my instances to support only Windows authentication, all I would need to do is click on the \u201cWindows authentication mode\u201d button in Figure 2, and then click on the \u201cOK\u201d button to apply that change. After making this property change, I would need to restart my instance for this change to take effect.<\/p>\n
SQL Server logins<\/h2>\n
In order to connect to SQL Server, one must have access to SQL Server. Access is granted via a login<\/em>. A login is also known as a security principal<\/em>, and is stored in the master database. There is one exception, and that is accessing a contained database. With contained databases users connect directly to database without the need for a login in the master database. More on contained databases in future articles.<\/p>\n
There are three types of logins that are stored in the master database: Windows user, Windows group, and SQL. Let\u2019s review each of these different types of logins.<\/p>\n
A Windows user login provides access for a single Windows user. When creating this type of login, no password is needed when defining the login in SQL Server. This type of login requires the user to first validate their login by logging into the Windows domain. The Windows domain stores the password.<\/p>\n
A SQL Server login is similar to a Windows login in that it provides access to SQL Server for a single user, but it is different than a Windows login because the password for a SQL login is stored in the master database. Therefore, when setting up a SQL Server login, a password needs to be provided for the login along with a few other password options, as shown in Figure 3.<\/p>\n
<\/p>\n
Figure 3: Setting up a SQL Server Authenticated Login<\/strong><\/p>\n
Figure 3 shows that a SQL Server Login can be enabled to enforce Windows password policies and expiration and can require a user to change the password upon their first login. Microsoft added these new password features when SQL Server 2005 was released. For applications to support these new password features, they can use the NetValidatePasswordPolicy<\/em><\/a> API.<\/p>\n
The last type of login, a Windows group login, is similar to a Windows login but slightly different. A Windows group login provides access to a SQL Server instance for every Windows login that is a member of the group. Windows groups are a great way to provide access to many Windows logins with only having to define a single login in SQL Server. Using a Windows group, access to the SQL Server instance can be maintained by adding or removing group members. Using Windows groups helps minimize security management efforts and troubleshooting login related security issues.<\/p>\n
If you look at the bottom of the screenshot in Figure 3, you will notice a \u201cDefault Database\u201d setting for a login. The default database setting when creating a login is the \u201cmaster\u201d database. When setting up a login, the default database can be changed to any database on the server. Best practice is to set the default database to a database that the user will use when connecting to SQL Server.<\/p>\n
Windows logins are considered more secure because of the way the password for the login is stored. The password for a Windows login is stored using true encryption. Where as the password for a SQL Login is not encrypted, instead it is hashed. Because a SQL login is hashed makes it easier to crack the password. Windows logins also require domain admin\u2019s to set up the login and password, where as SQL logins the database administrators set up the login and password. By having the domain admin\u2019s managing login passwords provides another layer of security, commonly called separation of duties. By separating the duties of creating and managing Windows logins from managing databases and access to databases, provides an additional security controls to gain access to data stored in SQL Server.<\/p>\n
Creating a login to SQL Server allows users to connect to SQL Server. But a login alone doesn\u2019t provide users access to any data in the different databases on the server. For a login to read and\/or write data to a database, the login will need access to one or more databases. A login can be set up to have access to many databases on an instance if required.<\/p>\n
Database users<\/h2>\n
A database user is not the same as a login. A login provides to a user or application the ability to connect to a SQL Server instance, whereas a database user provides the login rights to access a database. Each database a login needs access to will require a database user to be defined, except when a login has been given sysadmin rights. When a login has sysadmin rights, they have access to all database, without being mapped to a database user. This association between a login and a database user is known as a user mapping. User mappings for a login can be created during login creation or later for logins that have already been set up.<\/p>\n
Creating a database user while creating a new login<\/h3>\n
To show how to provide user mapping when a new login is created, I will create a new SQL Server login named \u201cRed-Gate\u201d. The screenshot shown in Figure 4 shows the \u201cLogin \u2013 new\u201d window where I will define this new login. To bring up this window, I expand the \u201cSecurity\u201d tab under my instance, and then right-click on the \u201cLogins\u201d option and then select the \u201cNew Login\u2026\u201d item from the drop-down.<\/p>\n
\"SQL<\/p>\n
Figure 4: Creating Red-Gate Login<\/strong><\/p>\n
In figure 4, I enter \u201cRed-Gate\u201d for the login name and entered the password for this SQL login in the dialog boxes provided. To provide database access for this new login, I click on the \u201cUser Mapping\u201d option in the left pane. When I do this, the window in Figure 5 is displayed.<\/p>\n
<\/p>\n
Figure 5: User Mapping Window<\/strong><\/p>\n
A red box shows the list of databases in Figure 5, where my new login can be mapped. In order to map my \u201cRed-Gate\u201d login to the \u201cAdventureWorks2019\u201d database, I would just need to click on the \u201cMap\u201d checkbox next to the AdventureWorks2019 database. When I do that, the screenshot in Figure 6 is displayed.<\/p>\n
<\/p>\n
Figure 6: Mapping Login to database<\/strong><\/p>\n
After checking the Map box, the name \u201cRed-Gate\u201d is automatically populated in the \u201cUser\u201d column for the AdventureWorks2019 database. This interface automatically generates a database user name the same as the login to which it is mapped. Database user names don\u2019t need to be the same as the login. If I wanted my database user name to be different, I could just type over the \u201cRed-Gate\u201d name and specify whatever database user name I wanted. Mapping a login to a database users only provides that login access to the database, but it doesn\u2019t give the login access to read or update data in the database. In future articles I will be discussing how to provide read\/write access to database objects.<\/p>\n
Suppose I wanted to map my new \u201cRed-Gate\u201d login to additional user databases. In that case, I could do that by just checking on another \u201cMap\u201d checkbox next to the additional databases. For this example, I only want to map my new \u201cRed-Gate\u201d login to the \u201cAdventureWorks2019\u201d database. To finish up mapping my \u201cRed-Gate\u201d login to the \u201cRed-Gate\u201d database user I just need to click on the \u201cOK\u201d button.<\/p>\n
Creating a new database user and mapping it to an existing login<\/h3>\n
There are times when a login has already been created, and it just needs access to one more database. For example, suppose I now wanted my established Red-Gate SQL Server login to access the database named \u201cMyDatabase\u201d. To give the Red-Gate login additional database access, I have a number of options. One option would be to just modify the user mappings by changing the properties on the login. This would be similar to how I just added the user mapping when I created the Red-Gate login.<\/p>\n
Another option is to add a new database user to the \u201cMyDatabase\u201d and then mapping that new database user to the Red-Gate login. To create a new user in the database \u201cMyDatabases\u201d I would first expand the database, right-click on the \u201cSecurity\u201d item, hover over the \u201cNew\u201d item, and then click on the \u201cUser\u2026\u201d item, as shown in Figure 7.<\/p>\n
<\/p>\n
Figure 7: Bring up the new database user dialog<\/strong><\/p>\n
When I click on the new \u201cUser\u2026\u201d menu item, the window in Figure 8 is displayed.<\/p>\n
<\/p>\n
Figure 8: Adding a new database user<\/strong><\/p>\n
To give the Red-Gate login access to MyDatabase, I need to fill out the form in Figure 8. The first item in Figure 8 to consider is the \u201cUser Type\u201d. This field defaulted to \u201cSQL User with Login\u201d. There are four other types: SQL user without login, User mapped to a certificate, User mapped to an asymmetric key, and Window users. Since I am creating a database user that will be mapped to a SQL login, I use the default. Next, I enter the database user name for the user I am creating. It could be any name, but I prefer to make the database user name match the same name as the login it is associated with. Therefore I enter \u201cRed-Gate\u201d in the \u201cUser Name\u201d field. I next map my new users to a login. To do the mapping, I can either type in \u201cRed-Gate\u201d for the login or use the ellipsis button (\u2026) to browse through the list of logins already created and select one.<\/p>\n
The last item needed is to define a default schema for this login. A schema name is associated with a database object collection owned by a database user. By default, each database has a schema named \u201cdbo\u201d owned by the \u201cdbo\u201d user account. You don\u2019t need to specify a schema when you define a new database user. If one is not specified when defining the database user, the \u201cdbo\u201d schema will be the default schema. Since this article is just a primer, I will discuss the different aspects of schemas. I\u2019ll leave that for another article. When I create my new Red-Gate database user, I\u2019ll leave the default schema item empty and let the create new users process set the default schema automatically to \u201cdbo\u201d.<\/p>\n
Once I\u2019ve created my new user, I can verify it exists in the database by expanding the \u201cUser\u201d item under the \u201cSecurity\u201d folder in Object Explorer. You can also create a new database user and map it to a login using a script. Listing 2 is an example of using TSQL to create the same login I just created using the point and click method.<\/p>\n
USE [MyDatabase]\r\nGO\r\nCREATE USER [Red-Gate] FOR LOGIN [Red-Gate]\r\nGO<\/pre>\n
Listing 2: Create a Red-Gate database user using a TSQL script<\/strong><\/p>\n
SQL Server authentication methods, logins, and database users<\/h2>\n
To connect to SQL Server, a person or process needs to authenticate. There are two different methods to authenticate to SQL Server: Windows and SQL Server. Windows is the more secure and recommended method for connecting to SQL Server. Each connection that authentications to SQL Server gains access to the instance via a login. Logins are defined at the server level. Logins by themselves don\u2019t provide access to data within SQL Server. To gain access to data in a database, a login needs to be mapped to a database user. The authentication methods, logins, and databases users provide the basic security foundations for SQL Server security.<\/p>\n
If you liked this article, you might also like\u00a0Understanding SQL Server Recovery Models.<\/a><\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
Understanding SQL Server security is a critical skill. Greg Larsen explains SQL Server authentication methods, logins, and database users in this article.…<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[137088,137087,5134,4696],"coauthors":[11330],"class_list":["post-89986","post","type-post","status-publish","format-standard","hentry","category-learn","tag-database-user","tag-server-login","tag-sql-prompt","tag-sql-server-security"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89986","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=89986"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89986\/revisions"}],"predecessor-version":[{"id":89999,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89986\/revisions\/89999"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=89986"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=89986"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=89986"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=89986"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}