SQL Server authentication modes

 SQL Server supports two types of authentications: Windows Authentication and SQL Server Authentication.

Windows Authentication: In Windows Authentication, the SQL Server instance trusts the Windows operating system to authenticate users. For example, consider a Windows domain environment where users have Windows user accounts. To allow these users to connect to a SQL Server instance using Windows Authentication, you would simply grant them access to the SQL Server instance using their Windows credentials.

SQL Server Authentication: In SQL Server Authentication, users are authenticated by the SQL Server instance using a username and password. For example, consider a scenario where you have users who do not have Windows user accounts. To allow these users to connect to a SQL Server instance, you would create SQL Server logins and assign passwords to them. The users can then connect to the SQL Server instance using their SQL Server login name and password.

Here's an example of how to create a SQL Server login and user using SQL Server Authentication:

-- Create a SQL Server login named "Harsh" with a password

CREATE LOGIN Harsh WITH PASSWORD = 'Password1', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;

-- Create a user named "Harsh" in the "Northwind" database

USE Northwind;

CREATE USER Harsh FOR LOGIN Harsh;

-- Grant SELECT permissions to Harsh on the "Orders" table

GRANT SELECT ON Orders TO Harsh;

Note: You must have the necessary permissions (such as the sysadmin or securityadmin role) to create logins and users in SQL Server. Additionally, the example above assumes that the "Northwind" database and the "Orders" table already exist.

You can determine which authentication methods are supported by a SQL Server instance by using the following steps:

    Open SQL Server Management Studio and connect to the SQL Server instance.

    In the Object Explorer, right-click on the instance name and select "Properties".

    In the SQL Server instance properties window, select the "Security" page.

    The supported authentication methods will be displayed under "Server authentication".

If the "Windows Authentication mode" option is selected, it means that only Windows Authentication is supported. If the "SQL Server and Windows Authentication mode" option is selected, it means that both Windows Authentication and SQL Server Authentication are supported.

You can use the following Transact-SQL script to determine which authentication methods are supported by a SQL Server instance:

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')

WHEN 1 THEN 'Windows Authentication'

WHEN 0 THEN 'SQL Server and Windows Authentication'

END AS [Authentication Method];

This script uses the SERVERPROPERTY function to retrieve the value of the "IsIntegratedSecurityOnly" property. A value of 1 indicates that only Windows Authentication is supported, while a value of 0 indicates that both Windows Authentication and SQL Server Authentication are supported.

You can run this script in SQL Server Management Studio by connecting to the SQL Server instance and executing it in a new query window. The result will show the supported authentication methods for the SQL Server instance.

Note: The authentication methods supported by a SQL Server instance can be changed by a member of the sysadmin role. If you are unsure about the authentication methods supported by your SQL Server instance, you should consult with your database administrator.