DEV Community

shariaretanvir
shariaretanvir

Posted on

Enable SQL Server Authentication in SQL-server

Step by step process for enable SQL server authentication.

SQL-server is a very popular RDBMS. After installation the authentication mode is by default set to Windows Authentication. But in real scenario its not recommended for security purpose. SQL-server also give us the option for SQL-authentication. So today I will show how to configure SQL- authentication in SQL-Server.

Step by step process

step 1. First login to sql server using windows authentication.

1

step 2. Now we need to go to the properties of sql server instance.

2

step 3. After that go to security tab and check SQL server and windows authentication mode and click OK.

3

step 4.Now we need to create a user who will access our database as a SQL authentication. For that go to security -> login -> new login options.

4

step 5. Now give a user name and set up the password for that user.
5

step 6. Now give the user role as per requirements. For example I give all the permission for this testuser.
6

step 7. Also set up the status tab just like this. Permission will be grant and login will be Enabled.

7

So all the necessary setting is done for our new user. Click on the OK button.

Now restart the SQL server and try to login using SQL server authentication mode. Give the user login and password. You will login this time.

#Points of Interest

Sometimes SQL-server can not login and shows some error like this user can not login. For this case you can restart the sqlexpress from the services. After that try to login again. This will solve the error.
8

So after login you will see that the new SQL user is logged in.
10

That's it. Now you can login in to SQL server using SQL authentication as well as windows authentication .

Discussion (0)