When dealing with SQL Server, there are two different ways to connect to the database engine:
- Windows Authentication
- SQL Server Authentication
Windows Authentication is simple. It means that if you're in a corporate environment, once you have logged in to your machine and have authenticated yourself via Active Directory, you do not have to provide your credentials again to applications that support Windows Authentication. This is otherwise known as "Single Sign On." You can achieve this same "Single Sign On" functionality on your own machine by installing SQL Server Express. By default, the installation will add your windows username as an "SA" or System Administrator to your SQL Server instance (usually .\sqlexpress). Then, when you launch SQL Server Management Studio to connect to your local SQL Server instance, you will be prompted with a login box where you can select SQL Server Authentication or Windows Authentication. Selecting Windows Authentication will use your machine username and password that you used when you logged in to your machine. SQL Server Authentication will prompt you for a username and password that you will have to type in.
So...when you configuring your application to connect to SQL Server, you'll need a connection string. You can find a valid connection string from http://www.connectionstrings.com. If you're using a connection string that contains a username and password, then you are using SQL Server Authentication to connect to the database. This username and password is the same username and password combination that you would use if you were connecting to your database engine from launching SQL Server Management Studio. If your connection string contains something like "Trusted_Connection = True" or "Integrated Security = SSPI" and does not contain a username and password, then you are using Windows Authentication to connect to the database. In fact, you cannot use a username and password and "Trusted_Connection = True" or "Integrated Security = SSPI" because you will get an error.
Now, after you're connection string has been utilized, you should be able to run your SELECT statements on your database without issue. This is because you are already authenticated on the SQL Server Instance. Most of the time, however, especially if you're developing an internet application (as opposed to an intranet application), you will also want to provide authorization (as opposed to authentication) to your database. This means that the user has already been authenticated (done by SQL Server), but still has to be authorized (done by your application).
In order to provide this authorization, most applications will present a login screen to the user in order to obtain something like a username and password. This username and password that the user types in will be sent to the database to be checked against a "Users" table (or whatever you have called it in your application) to see if any entries in there match. If it matches, then the application will tell the user that they are now authorized to use the application and it will take them to the screen that they typically see after logging in.
Think of Facebook, for example. When you visit facebook.com, you are presented with a login screen in the top right corner where you are asked for your email address and password. At this point, the application has most likely already been authenticated on the database backend, but is asking for your credentials so that it can authorize who you are. Once you have typed in your username and password, it will query Facebook's "Users" table to see if it finds a match with the email and address provided. If there is a match, then it authorizes you to use the Facebook application and shows you your Newsfeed screen.
Hopefully this helps you understand the different between Windows Authentication and SQL Server Authentication. If you need help, post a comment!
No comments:
Post a Comment