ISSC431 CTD Windows Authentication and A SQL Server Authentication Discussion
Hello,
I need two responses of at least 150 words each for the below students discussions for this week. Also in the bold below are the questions the students at answering.
For this assignment, answer the following:
What is the difference between a Windows Authentication and a SQL Server Authentication?
How does SQL Server perform authentication when a user connects from a nontrusted connection with a specified logon name and password?
Why is it not advisable to enable the SA account in SQL Server?
Student one:
Part I
In Microsoft SQL Server, user credentials can be authenticated using Windows Authentication or SQL Server Authentication. Windows authentication is the default mode selected during an initial SQL Server install. This mode is thought to be the more secure option for authentication purposes. Windows Authentication uses the Windows operating system to authenticate users attempting to access a SQL Server database. An authorized user is immediately granted access to their SQL Server accounts after signing into Windows. This mode eliminates the need to provide separate credentials when logging into SQL Server. Next, SQL Server Authentication uses database authentication to verify a user’s credentials. This method authenticates an individual based on the information stored within the SQL Server master database. In some cases, SQL Server Authentication may require a user to create separate local database accounts to access the database on different machines (Basta & Zgola, 2012).
Part II
Anytime a user attempts to connect using a specified login name and password from a nontrusted connection, SQL Server conducts authentication by retrieving account information and comparing it to the credentials submitted by a user. When SQL Server performs this process, it will verify that a user’s account has been established and ensures that a password entered by a user matches the password recorded in the database (Microsoft, 2017). If a person enters their credentials incorrectly, then authentication will fail.
Part III
One should not enable the SQL Server Systems Administrator (SA) account because it links to the system administrator fixed server role, which provides the user with irrevocable administrative control over SQL Server (Microsoft, 2018). If an unauthorized user gains access to this account, they can cause permanent changes to SQL Server. These permanent changes may also occur if an individual makes a mistake while signed into this account. Also, it is a good practice to disable the SA to maintain a principle of least privilege, which ensures that there aren’t any active accounts that have full control over SQL Server. By default, the SA is disabled when Windows Authentication mode is used because it is not needed (Basta & Zgola, 2012). When the SA account is enabled, extreme caution and care must be taken to secure SA credentials.
References
Basta, A. & Zgola, M. (2012). Database Security. [VitalSource Bookshelf]. Retrieved from https://online.vitalsource.com/#/books/97813053284″¦
Microsoft. (2017). Connect to Server (Database Engine) – SQL Server. Retrieved June 5, 2019, from https://docs.microsoft.com/en-us/sql/ssms/f1-help/”¦
Microsoft. (2018). Authentication in SQL Server. Retrieved June 5, 2019, from https://docs.microsoft.com/en-us/dotnet/framework/”¦
-Jeremy
Student two:
It is almost over our time is moving way to fast. Anyway, onward to the question at hand.
What is the difference between a Windows Authentication and a SQL Server Authentication?
It looks like the difference between a Windows Authentication and a SQL Server Authentication is Windows resides within Active Directory for the domain; whereas, SQL Server checks the Active Directory verifies or make sure that there is an active account with a working password while also checking for the level of permission the users has to be able to gain access to the server. Since the SQL server does not reside on the domain, it allows management the ability to create one account or even set up a group account. Another way Windows uses authentication is by a series of encrypted messages that are used in the authenticating users within the SQL Server. Plus, users that are already logged onto the system do not need to log onto any separate SQL Server without a password or user name. (n.a., 2017)
How does SQL Server perform authentication when a user connects from a non-trusted connection with a specified login name and password?
Logins usually take place in situations where the user uses an SQL Server connection that is untrusted. The same type of login happens if you workgroups or with Internet applications like ASP.net. It just so happens that there are three types of Login types the local Windows, groups, and SQL Server logins. In the local Windows, the user has a trusted domain account or an account, and Windows relies on the SQL Server
