How to setup Windows Authentication for SQL Server

Follow

When connecting to SQL Server there are 2 different types of authentication, Windows Authentication and SQL Authentication.

Windows Authentication means that the users Windows login is used and is added as a user within SQL. For domains the user is authenticated through Active Directory and for workgroups they are authenticated through the local users on that machine.

Benefits:

  • More secure
  • No more troubleshooting firewall issues due to blocked ports
  • No more worrying about viruses changing the sa password

Cons:

  • Workgroups = A little more involved setup
  • If the user is setup as a Limited User in Windows on their machine they won’t be able to click the drop down menu in ESC and see the available databases however they can type it in manually. A workaround that you can use is to right click on the ESC icon on the desktop and then click Run as or Run as Administrator. Once you run ESC as the administrator you will be able to see the available databases to troubleshoot the problem.

Before You Begin:

Before proceeding, you must determine whether your network is setup as a domain or a workgroup. This can easily be determined by going to Start and then type in “sysdm.cpl“ in the Search box and then click “OK”. Once the System Properties box displays, click on the Computer Name tab and then click on the "Change" button. You will either see that the computer is on a domain or workgroup from this screen. Click "Cancel" to close the window.

Details:

Adding a User Into SQL Through ESC (MUST BE DONE ON THE SERVER):

The customer is in a domain

  1. Go to the server machine that has SQL Server running.
  2. Launch ESC and log in as the ADMIN user.
  3. Go to Company → Setup Users and then click “Add New”.
  4. Click the “Find User” button. A list of all the users in Active Directory within the domain will appear in the list.
  5. Click on the user that represents the user we’re adding into ESC and then click OK. When you click OK it takes that information and fills in the blank on the original page. You could of course fill in the information manually if you knew it but it’s easier to let ESC query the list so there’s no mistakes made.
  6. Once the information is populated you can click “Next” and then continue going through the wizard entering the information you normally would and assigning the permissions for that user. IMPORTANT: The Windows user name from Active Directory DOES NOT have to match the ESC user name you enter nor does the password have to match; Windows authentication and ESC authentication are completely different and do not reference each other at all.
  7. Repeat steps 3 – 6 for each user that needs to be setup.

The customer is in a workgroup

  1. Have someone in the network gather a list of the Windows users and passwords on each machine that will be using ESC and create those users on the server machine. These new users may be setup as Limited Users for security purposes. If a customer does not know how to setup new users then we recommend contacting a local IT person as the setting up Windows users is beyond our support. 
  2. Go to the server machine that has SQL Server running.
  3. Launch ESC and log in as the Admin user.
  4. Go to Company → Setup Users and then click “Add New”.
  5. Click the “Find User” button. A list of all the local users on that machine will appear in the list.
  6. Click on the user that represents the user we’re adding into ESC and then click OK. When you click OK it takes that information and fills in the blank on the original page. You could of course fill in the information manually if you knew it but it’s easier to let ESC query the list so there’s no mistakes made.
  7. Once the information is populated you can click “Next” and then continue going through the wizard entering the information you normally would and assigning the permissions for that user.
    IMPORTANT: The local Windows user name from DOES NOT have to match the ESC user name you enter nor does the password have to match; Windows authentication and ESC authentication are completely different and do not reference each other at all.
0 people found this helpful

Comments

0 comments

Please sign in to leave a comment.