Login page with MS SQL for C#

Question

Dear all i want to create a login page for my ppc application in C#. basically i wll link the login page to SQL server for username and password verification. so i want to know how we write the code to verify the password at SQL server asuming my server name is tech and my database is password so we should have somethign like this sqlConnection = new SqlConnection; Data Source=tech Initial Catalog=password; but the thing is how to verify the password ??? Thanks all !

Vijaya Kadiyala · Answer

Hi Check out the below link http://www.daniweb.com/forums/thread24148.html Thanks -- Vj http://dotnetvj.blogspot.com

pacifiermax · Answer

yo man i have done it.. althought not using the method u show me but thanks alot for ur efforts really appreciate it ;)

pacifiermax · Answer

yo man thanks !!! i get what u mean now so whenever i want to check the password i jsut call the validatapassword object.. cool i will try 6 hours later.. coz its 1 am my side now.. thanks for all your help .. once i try i will let u know here... thanks !

ytram5 · Answer

Ok here it is: ... if(ValidatePassword(txtUserID.Text txtPassword.Text) { frmMain main = new frmMain(); main.Show(); this.Hide(); } ... This is the function ValidatePassword: public bool ValidatePassword(string user string pass) { conSql2000 = new SqlConnection ( Server=198.162.0.2;IntegratedSecurity=SSPI;Database=testing;user ID = test;Password=atest123 ); SqlCommand cmd = new SqlCommand (SELECT lecIDpassword FROM test  + WHERE username = @Username AND password = @Password); cmd.Parameters.Add(@UsernameSqlDbType.Varchar50).Value = txtUserID.Text; cmd.Parameters.Add(@Password SqlDbType.Varchar50).Value = txtPassword.Text; cmd.Connection = conSql2000; cmd.Connection.Open(); try { SqlDataReader = cmd.ExecuteReader(); if(sqr.HasRows) { sqr.Close(); return true; } else { sqr.Close(); return false; } } catch(Exception exc) { throw exc; } finally { cmd.Connection.Close(); } } } Now you can call ValidatePassword anytime you need it.

pacifiermax · Answer

or can we use something like this ConStr = Data Source = ServerName; User Id =  + this.txtUserName.Text + ; Password =  + this.txtPassword.Text + ; Initial Catalog = TestDataBase; System.Data.SqlClient.SqlConnection MyConn = new SqlConnection(ConStr); ??? thanks !

pacifiermax · Answer

what do u mean ? sorry i am a newbies.. u mean we take the current code and use it ?

ytram5 · Answer

It's just taking the current code we've talked about and putting into a method you can call over and over again rather than re-writing it each time.

pacifiermax · Answer

As for an access layer this would be comprised of a class library that abstracts certain functions. For example in the class library you could have a static method that is just ValidatePassword(string user string pass) and then you do all of the password validation in that method and return true if you determine that the username and password are correct or false if not. this sound great do you have any sample code for this ? i think this will solve my problem Thanks !

ytram5 · Answer

Ok instead of .HasRows try: if(sqr.Read()) { //do it } As for an access layer this would be comprised of a class library that abstracts certain functions. For example in the class library you could have a static method that is just ValidatePassword(string user string pass) and then you do all of the password validation in that method and return true if you determine that the username and password are correct or false if not.

pacifiermax · Answer

yep.. i have instantiate the SqlDataReader object but still can't get it .. so far this is teh last error...  As for making it easier I don't know of any way. A good design would probably include an access layer where a username/password validation would only take one line of code but that doesn't actually make it easier as you still have to write the code but in that case you would only have to do it once. can you show me how it works? Thanks !

ytram5 · Answer

If you are able to instantiate the SqlDataReader object then you should have access to all it's properties without any additional references AFAIK. As for making it easier I don't know of any way. A good design would probably include an access layer where a username/password validation would only take one line of code but that doesn't actually make it easier as you still have to write the code but in that case you would only have to do it once.

pacifiermax · Answer

yeah .. i am using VS.net 2003 do i have to declare anything or add any refenrecnce? Thanks ! btw any other eariser way ?

ytram5 · Answer

Are you using VS.NET 2003? Because the HasRows property is definitely working in my projects.

pacifiermax · Answer

ok i did SqlDataReader sqr = cmd.ExecuteReader(); now got one more error.. if(sqr.HasRows) 'System.Data.SqlClient.SqlDataReader' does not contain a definition for 'HasRows' Thanks man !

pacifiermax · Answer

do u have any idea to make it simple ? its ok for the security issue all i want it to do is.. verify the password from SQl so when wrong password key in it will display error message... when the correct matching username and password key in it will bring it to the next form .. so if posibble keep it as simple as possible.. thanks man ! appreciate it ! ;)

ytram5 · Answer

You have to assign a name to the SqlDataReader like so: SqlDataReader sqr = cmd.ExecuteReader();

pacifiermax · Answer

ok thanks ! SqlDataReader = cmd.ExecuteReader(); 'System.Data.SqlClient.SqlDataReader' denotes a 'class' where a 'variable' was expected if(sqr.HasRows) The type or namespace name 'sqr' could not be found (are you missing a using directive or an assembly reference?) sqr.Close(); The type or namespace name 'sqr' could not be found (are you missing a using directive or an assembly reference?) sqr.Close(); The type or namespace name 'sqr' could not be found (are you missing a using directive or an assembly reference?) Thanks man !!!! appreciate it !

ytram5 · Answer

I can't ICQ or AIM I am at work currently. Post your errors and your complete code block and I'll try to help.

pacifiermax · Answer

i getting many errors only the sqr. command.. should er declare it 1st.. ? btw can u add me into ur icq or yahoo? icq : 299599129 yahoo messenger : rfbies it would be easier to talk at messenger appreiate it !

pacifiermax · Answer

yo man i got many error over the code u given m e.. is there any easy way . . do u have icq or yahoo messenger ? thansk !

ytram5 · Answer

First of all in your query you are calling txtUserID and txtPassword. I'm assuming these are text box objects as you use txtUserID.Text in the code block after the SqlCommand. When you are using them in your query you will need to call the .Text property of those text boxes. Second of all if you are concerned about security at all you will want to use parameterized SQL commands. Here's an example using your current code: conSql2000 = new SqlConnection ( Server=198.162.0.2;IntegratedSecurity=SSPI;Database=testing;user ID = test;Password=atest123 ); SqlCommand cmd = new SqlCommand (SELECT lecIDpassword FROM test  + WHERE username = @Username AND password = @Password); cmd.Parameters.Add(@UsernameSqlDbType.Varchar50).Value = txtUserID.Text; cmd.Parameters.Add(@Password SqlDbType.Varchar50).Value = txtPassword.Text; ... Be sure to replace the '50' I put in there with the actual length of the data types you have in your table. Next you will have to set the command's connection to the connection you created and then open it: cmd.Connection = conSql2000; cmd.Connection.Open(); Now that you have done this you can attempt to read your results. Since you have passed both the username and password the best way to do this would be to instantiate a SqlDataReader object and determine if it has any rows. This will only have rows if there is a username and password that exists matching the two parameters you passed. try { SqlDataReader = cmd.ExecuteReader(); if(sqr.HasRows) { sqr.Close(); frmMain main = new frmMain(); main.Show(); this.Hide(); return; } else { sqr.Close(); MessageBox.Show(Invalid username or password!); } } catch(Exception exc) { MessageBox.Show(exc.ToString()); } finally { cmd.Connection.Close(); } Notice how I put a try/catch/finally block in right before the SqlDataReader is instantiated. If you do this you can call the cmd.Connection.Close() in the finally block thus insuring that it will be closed regardless of an exception being thrown. It is also important to note that unless you have set the collation of the Password column in your users table the password will NOT be case-sensitive. To change the collation go into the table designer in Enterprise Manager go to the password column and change the collation. Another alternative is to retain the current collation and retrieve the password in your query. When you retrieve it it will retain case and you can read the value and compare it to the value in your txtPassword object.

miker · Answer

From ConnectionStrings.com: Data Source=190.190.200.1001433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd; (DBMSSOCN=TCP/IP instead of Named Pipes at the end of the Data Source is the port to use (1433 is the default))

pacifiermax · Answer

i still can't get it work  now the conenction can be establish .. meaning i can retireve the data taht i want. . but how to use it as a verification ? i have try this.. conSql2000 = new SqlConnection ( Server=198.162.0.2;IntegratedSecurity=SSPI;Database=testing;user ID=test;Password=atest123); SqlCommand cmd = new SqlCommand (SELECT lecID password FROM test + WHERE username = '+txtUserID +' AND password = '+ txtPassword + ' ); if(txtUserID.Text==lecID) { frmMain main = new frmMain(); main.Show(); this.Hide(); return; } can anyone help me? can show me the coding ? thanks !

ytram5 · Answer

Also on another note always use parameterized SQl statements when doing this kind of query. Whether you do it inline or with a stored procedure it is a great deal more secure as the database itself will do validation of the entered form values. You can see where I did that with the @Username parameter. If you don't do this you have a chance of users injecting SQL statements within their input causing dangerous and unpredictable results.

ytram5 · Answer

Ahh sorry I misunderstood. Simply have a table that stores the username and password. Select the password for the provided username and verify that it matches the password. Here's an example code block: SqlCommand cmd = new SqlCommand(); cmd.CommandText = SELECT Password  + WHERE Username = @Username; cmd.Parameters.Add(@Username SqlDbType.VarChar50).Value = this.txtUsername.Text; cmd.Connection = new SqlConnection(Your connection string); cmd.Connection.Open(); SqlDataReader sqr = cmd.ExecuteReader(); string pass = string.Empty; if(sqr.Read()) { pass = sqr.GetString(sqr.GetOrdinal(Password)); } sqr.Close(); cmd.Connection.Close(); if(pass == string.Empty) { //This should only happen if the query didn't find anything this.Label1 = Invalid username; } else if(pass != this.txtPassword.Text) { //This should only happen if the password doesn't match this.Label1 = Invalid password; } else { //Login successful! this.Label1 = Login successful!; } This example assumes you have two text boxes txtUsername and txtPassword where the user is entering their information. It also assumes you have a label Label1 where feedback is being provided to the user. If your login form is for a system with fairly sensitive data you should look into encryption alternatives rather than just storing a password in plain text. I have worked with companies that store this kind of information in plain text and the data is VERY sensitive but it's just not a good idea.

pacifiermax · Answer

hi i think u misunderstanding what i ask.. i am actually asking a login page with sql password verification not sql password.. hehehe Thanks !

ytram5 · Answer

You will want to use a connection string. With the items you listed Data Source=tech and Initial Catalog=password you will also want to add User ID = username; Password=password. Once you have this connection string built you can try to open the connection. If the username and password are not correct a SqlException will be thrown.