Geekpedia Tutorials Home

Building a C# Chat Client and Server

Building a C# Chat Client and ServerA step by step tutorial teaching you how to create your own chat client and chat server easily in C#, for local networks or the Internet.

in C# Programming Tutorials

Getting Hard Drive Information

Getting Hard Drive InformationA C# tutorial showing you how to make use of WMI to extract information on disk drives, such as model, capacity, sectors and serial number.

in C# Programming Tutorials

UPS Shipping Calculator

UPS Shipping CalculatorThis tutorial will teach you how to calculate the shipping cost based on the weight, height, length and depth of the box, the distance and the UPS service type.

in PHP Programming Tutorials

Create Your Own Rich Text Editor

Create Your Own Rich Text EditorCreating a Rich Text Editor using JavaScript is easier to do than you might think, thanks to the support of modern browsers; this tutorial will walk you through it.

in JavaScript Programming Tutorials
Search
Tutorials
Programming Tutorials
IT Jobs
From CareerBuilder

What is the difference between Windows Authentication and SQL Authentication?

On Friday, June 9th 2006 at 05:00 AM
By Andrew Pociu (View Profile)
****-   (Rated 3.3 with 26 votes)
Advertisement
More SQL Resources
If you are new to the Microsoft SQL Server environment, you probably encountered the possibility to choose between Windows Authentication and SQL Authentication.

SQL Authentication

SQL Authentication is the typical authentication used for various database systems, composed of a username and a password. Obviously, an instance of SQL Server can have multiple such user accounts (using SQL authentication) with different usernames and passwords. In shared servers where different users should have access to different databases, SQL authentication should be used. Also, when a client (remote computer) connects to an instance of SQL Server on other computer than the one on which the client is running, SQL Server authentication is needed. Even if you don't define any SQL Server user accounts, at the time of installation a root account - sa - is added with the password you provided. Just like any SQL Server account, this can be used to log-in localy or remotely, however if an application is the one that does the log in, and it should have access to only one database, it's strongly recommended that you don't use the sa account, but create a new one with limited access. Overall, SQL authentication is the main authentication method to be used while the one we review below - Windows Authentication - is more of a convenience.

Windows Authentication

When you are accessing SQL Server from the same computer it is installed on, you shouldn't be prompted to type in an username and password. And you are not, if you're using Windows Authentication. With Windows Authentication, the SQL Server service already knows that someone is logged in into the operating system with the correct credentials, and it uses these credentials to allow the user into its databases. Of course, this works as long as the client resides on the same computer as the SQL Server, or as long as the connecting client matches the Windows credentials of the server. Windows Authentication is often used as a more convenient way to log-in into a SQL Server instance without typing a username and a password, however when more users are envolved, or remote connections are being established with the SQL Server, SQL authentication should be used.
Digg Digg It!     Del.icio.us Del.icio.us     Reddit Reddit     StumbleUpon StumbleIt     Newsvine Newsvine     Furl Furl     BlinkList BlinkList

Rate Rate this Knowledge Base article
Comment Current Comments
by Cluestick on Monday, February 26th 2007 at 04:54 AM

Quote:"however when more users are envolved, or remote connections are being established with the SQL Server, SQL authentication should be used."

Are you kidding me? If you do that, you'll just have to administer the Windows UserIDs AND the SQL ones (users will have two username/passwords to remember and keep in sync).
What's wrong with using Windows Authenication (via Active Directory) for all SQL access?
It seems from your comment "as long as the connecting client matches the Windows credentials of the server" that you're unaware of the concept of a domain account that can have access to remote resources and a single point of access control.
Thwack.

by Cluestick on Monday, February 26th 2007 at 04:57 AM

http://www.microsoft.com/sql/prodinfo/previousversions/securingsqlserver.mspx

See Point3:
Use Windows Authentication Mode.

Whenever possible, you should require Windows Authentication Mode for connections to SQL Server. This will shield your SQL Server installation from most Internet-based attacks by restricting connections to Microsoft Windows user and domain user accounts. Your server will also benefit from Windows security enforcement mechanisms such as stronger authentication protocols and mandatory password complexity and expiration. Also, credentials delegation (the ability to bridge credentials across multiple servers) is only available in Windows Authentication Mode. On the client side, Windows Authentication Mode eliminates the need to store passwords, which is a major vulnerability in applications that use standard SQL Server logins.

To set up Windows Authentication Mode security with Enterprise Manager in SQL Server:

Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Authentication, click Windows only.

by karthik on Thursday, April 3rd 2008 at 03:23 AM

Can we use Windows Authentication for a web base application [please tell me for both intranet and internet based applications ] ?

by williams on Friday, December 5th 2008 at 09:20 AM

Can we use Windows Authentication for a web base application [please tell me for both intranet and internet based applications ]. I need the answer with explanation urgently.
Thanks

by williams on Friday, December 5th 2008 at 09:20 AM

Can we use Windows Authentication for a web base application [please tell me for both intranet and internet based applications ]. I need the answer with explanation urgently.
Thanks

by Sheff on Tuesday, February 24th 2009 at 08:10 AM

You can use Windows Authentication as long as you use impersonation within ASP.NET or run the web processes as a Windows user and have some other form of authentication at the front end of your website.

by Sheff on Tuesday, February 24th 2009 at 08:18 AM

Additionally the article is utter rubbish - SQL Authentication should only be used as a last resort, the method that should be used is Windows Authentication with a proper Active Directory, thereby removing the supposed need for "the client to reside on the same server" or "the connecting client matches the Windows credentials". For goodness sake Domains have been around for nearly as long as the SQL standard!

SQL Authentication is poor as it is clear text unless encrypted and requires the password to be held somewhere on the client / service that connects to the database (this is client is often Excel and as any hacker knows it can be easily extracted with notepad!!!)

by pradeep on Thursday, April 30th 2009 at 04:11 PM

Thank you so much for letting us know that windows authentication can be used for web applications. I was really bogged down when it said the web apps should have sql server authentication!

Thank you!

by Sheff on Friday, May 1st 2009 at 04:37 AM

It's worth noting that Windows Authentication at the client end should only really be used in an intranet situation. Web Form Authentication should be used for the client end if you're considering deploying an external website.

At the server side Windows Authentication should be the preferred method as this can be simply set in IIS using the Process Identity.

by sameer on Tuesday, November 17th 2009 at 12:55 PM

hi friends
I have successfully installed sql server 2005 enterprise edition but when I tried to open the sql server management studio program;it's asking for server name and windows authentication. know I dont know what should I put on the place of server name neither I have put any sql server authentication name or password.please help me out

by Billel on Sunday, December 5th 2010 at 05:13 AM

Hi williams, yes you can use Windows Authentication for a web base application all what you have to do is to add ASPNET user account to you SQL server connexions, don't forget to specify the database for which you web app have to connect to.

by akhilesh manjakatil on Monday, June 27th 2011 at 03:57 AM

Do we need admin rights on the machine after installing SQL Server to connect to the server using windows authentication.
I can connect using the sa id but cannot connect using windows authentication using the same machine where the server is installed..??

by credit card applications on Wednesday, December 14th 2011 at 10:23 AM

Why don't you post your own solution Pavlo..... it would have sure taken less time than writing all that.

by Sumit Rana on Thursday, February 2nd 2012 at 03:05 AM

in SQL Server.
Windows Authentication is for the environment that all of your users are part of a Windows domain (have a Windows NT/2000 user account or is a member of a group). Your access to SQL Server is controlled by your Windows NT/2000 account or group, which is authenticated when you log on to the Windows operating system on the client.

If you have a mixed environment (e.g. Windows and Novell), then you have to use SQL Server Authentication where you will have to create a Login ID and Password
Don't be confused with "Authentication" and "Authorization." The "Authentication" is dealing with user's connection to SQL Server and the "Authorization" is dealing with what data privilege a user has once he is connected to the SQL Server.

Secondly, don't be confused with "Authentication,"(think of it as a "method"), and "Authentication Mode." There are two kinds of authentication - Windows authentication and SQL Server authentication. And there are two kinds of authentication mode - Windows authentication mode and mixed authentication
mode.

The Windows authentication mode only allows you to login/connect to SQL Server with Windows authentication. The mixed authentication mode allows you to login/connect to SQL Server with either Windows authentication or SQL Server authentication.

Again, if your environment is pure Windows, then you should use Windows authentication mode, which uses Windows authentication to login/connect to SQL Server. If your environment is not pure Windows, like Novell and Windows, then you have to use mixed authentication mode, which will prompt you, during connection, to choose either Windows authentication or SQL Server authentication to connect to SQL Server.

If you are a member of Windows' administrator group then, it doesn't matter if you are using Windows authentication mode or mixed authentication mode, you should be able to use Windows Authentication to connect to SQL Server. However, what you can do after the connection depends on what you have been authorized.

by swati on Wednesday, March 28th 2012 at 08:04 AM

what is difference between windows authentication and sqlserver authentication? explain with example

by swati on Wednesday, March 28th 2012 at 08:05 AM

what is difference between windows authentication and sqlserver authentication? explain with example

by arun on Thursday, June 21st 2012 at 07:15 AM

the main difference is that, in sqlserver authentication the user should give user name and password to access the data. unlike this in windows authentication no password and username is allowed

by Venomrider on Thursday, June 21st 2012 at 07:18 AM

apart from the username and password , what else is trhe difference between the sql and windows authentication.
I have a gift to one who tell the answer.

by priya on Thursday, August 16th 2012 at 04:37 AM

apart from the username and password , what else is trhe difference between the sql and windows authentication.
I have a gift to one who tell the answer.

by priya on Thursday, August 16th 2012 at 04:37 AM

apart from the username and password , what else is trhe difference between the sql and windows authentication.
I have a gift to one who tell the answer.

by priya on Thursday, August 16th 2012 at 04:38 AM

apart from the username and password , what else is trhe difference between the sql and windows authentication.
I have a gift to one who tell the answer.

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:35 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:35 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:35 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:35 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:35 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:35 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:35 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:35 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:36 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:36 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:36 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:36 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:36 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication

by Kailas Prabhar Bedarkar on Saturday, October 13th 2012 at 07:36 AM

Mixed mode allows users to connect with either windows authentication or SQL authentication


Comment Comment on this Knowledge Base article
Name: Email:
Message:
Knowledge Base Related Knowledge Base Articles
There are no related KB articles.

Comment Related Source Code
There is no related code.

Comment Related Tutorials
There are no related tutorials.

Jobs SQL Job Search
My skills include:

Enter a City:

Select a State:


Advanced Search >>
Ads

From the creators of Geekpedia, a revolutionary new coupon website!

BargainEZ has coupons codes, printable coupons, bargains and it is the leading source of Passbook coupons for iPhone and iPod touch devices.

Coupons
Discover Geekpedia
Other Resources