Authentication not working!

Hey there,

I have a table [user] in a SQLExpress database [Database.mdf] in the App_Data folder. There are two columns. [userId] and [password]. I want to authenticate the user who wants to log in through a Login control. the user should have his userId and password in the [user] table. I digged and made my code. but it doesn't work it keeps giving me that the user is not authenticated. i guess it has no errors. please tell me if you find the error. here is the code:

1protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)2 {3 SqlConnection conExpress =new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=Database;Integrated Security=True;User Instance=False");//Create the server connection45try6 {7string id_user = Login1.UserName.Trim();//Get the username from the control8string pass_user = Login1.Password;//get the Password from the control910 //These are a sql and connection Examples11string sql ="SELECT userId, password FROM user WHERE userId = @.param_Id AND password = @.param_Password";12 SqlCommand comSQL =new SqlCommand(sql, conExpress);//Create the sql command using sql string and sql connection1314 //Add the sql parameters15 comSQL.Parameters.AddWithValue("@.param_Id", id_user);//New on VS 2.016 comSQL.Parameters.AddWithValue("@.param_Password", pass_user);1718string cod_user ="";19string name_user ="";2021//Open database connection22 conExpress.Open();23 SqlDataReader dr = comSQL.ExecuteReader();24while (dr.Read())25 {26 cod_user = dr.GetValue(0).ToString();//The coduser is unique onto database table27 name_user = dr.GetValue(1).ToString();28 }29 conExpress.Close();//Close Database Connection30if (cod_user !="")//The user exist onto database31 {32//Create the session vars33 Session["coduser"] = cod_user;34 Session["nameuser"] = name_user;35 e.Authenticated =true;//Grant the access, Goes to DestinationPageUrl36 }37 }38catch(Exception)//On Login Error39 {40 e.Authenticated =false;//Confirm that you are out41 conExpress.Close();//On any error case, close the database connection42 }43 }

The way I've used the Login control with custom authentication (without MembershipProvider) is

processing the event of the Login Control:

protected

void Login1_LoggingIn(object sender,LoginCancelEventArgs e)

and if the credentials is valid i create the autheentication cookie:

FormsAuthentication.SetAuthCookie(userName,false);

and Redirect to the page user has requested.

If the credentials are wrong I set :

e.Cancel =

true;

and

Login1.FailureText =

"Unable to validate credentials";

I'm not sure this is the right way of doing that, but it works for me.

Hope this will help you.

Cheers,

Yani

sql

Authentication an application using Windows Integrated Authentication

Hi all,

My work is using a shared application which accesses a MSSQL 2000 database. To access the application, the folder on the Windows 2003 Server is shared and users can access the folder through a shared drive.

For the application to access the database, it uses an ODBC connection to the MSSQL server which originally used the SA password.

We have recently switched to using Windows Integrated Authentication because we believe it offers a higher level of security. However the only way in which we have been able to enable this is to add the windows users to the SQL server.

The problem with this is that the application sets permissions for individual users on what records they can see within the database. We have found that by adding the windows users to the SQL Server, they can bypass the permissions the set by the application by simply using any application that can use an ODBC connection, such as Enterprise Manager, and see all the database.

One way around this would be to set up domains of users with access privileges to the tables which reflect the permissions set by the application, and configuring a view of the data so they may only see the records that they have permissions to. However to do this would require a high administrative cost to ensure that changes made in the application are reflected in the privileges of the SQL server.

Instead, is there a way the SQL server can authenticate that the ODBC connection is coming from the correct application using Windows Integrated Authentication?

This would allow the applcation to determine security, and stop users from connecting to the SQL server using other applications.

Alternatively, can the SQL server, using Windows Integrated Authentication, also ask the application to supply a username and password?

Any help with this matter would be greatly appreciated.

Thanks!

The answer to both your questions is no. Windows authentication does not authenticate the application that made the connection request, it just authenticates the context under which the connection request was made. Also, the whole purpose of Windows Authentication is to remove the need to provide a password, so if you want to use a password, you should just continue using SQL Authentication.

Also, your application should not control database access within itself. Database access should be controled in the database or in a mid-tier, not within the client application.

Thanks
Laurentiu

|||

Thanks Laurentiu,

I would love to have control over the application itself and do it and a more securely, but we didn't create the application, and trying to get the vendor to do it is like pulling teeth.

What I'm really after is the best security configuration for the application which will provide the highest level of security, and more importantly please my manager :)

The only things we have control over are whether the application uses the SQL Password or Windows Authentication, and who can access the folder with the application in it.

When using the SQL Password, the application can only log into the database using the one account, which basically must have rights to do everything.

Using Window Authentication, I have to add individual users to the SQL Server to allow the application to access the database when they are using the application. However, this will allow the user to use other applications to access the server, since they have rights to it.

What do you think is the best configuration?

Thanks.

|||

Where does the application store the sa password? If your users can easily get to it, they can connect as sa. Also, if they can debug the application, they can get the password and connect directly as sa. So the drawback of having the application connect as sa is that your users could figure out the sa password and can then become admins.

If you use Windows authentication, the users could connect on their own to the server, but they would probably not be administrators (unless the application makes any Windows login that is used with it an administrator).

I'm not sure which one of these you would prefer.

Thanks
Laurentiu

Authentication an application using Windows Integrated Authentication

Hi all,

My work is using a shared application which accesses a MSSQL 2000 database. To access the application, the folder on the Windows 2003 Server is shared and users can access the folder through a shared drive.

For the application to access the database, it uses an ODBC connection to the MSSQL server which originally used the SA password.

We have recently switched to using Windows Integrated Authentication because we believe it offers a higher level of security. However the only way in which we have been able to enable this is to add the windows users to the SQL server.

The problem with this is that the application sets permissions for individual users on what records they can see within the database. We have found that by adding the windows users to the SQL Server, they can bypass the permissions the set by the application by simply using any application that can use an ODBC connection, such as Enterprise Manager, and see all the database.

One way around this would be to set up domains of users with access privileges to the tables which reflect the permissions set by the application, and configuring a view of the data so they may only see the records that they have permissions to. However to do this would require a high administrative cost to ensure that changes made in the application are reflected in the privileges of the SQL server.

Instead, is there a way the SQL server can authenticate that the ODBC connection is coming from the correct application using Windows Integrated Authentication?

This would allow the applcation to determine security, and stop users from connecting to the SQL server using other applications.

Alternatively, can the SQL server, using Windows Integrated Authentication, also ask the application to supply a username and password?

Any help with this matter would be greatly appreciated.

Thanks!

The answer to both your questions is no. Windows authentication does not authenticate the application that made the connection request, it just authenticates the context under which the connection request was made. Also, the whole purpose of Windows Authentication is to remove the need to provide a password, so if you want to use a password, you should just continue using SQL Authentication.

Also, your application should not control database access within itself. Database access should be controled in the database or in a mid-tier, not within the client application.

Thanks
Laurentiu

|||

Thanks Laurentiu,

I would love to have control over the application itself and do it and a more securely, but we didn't create the application, and trying to get the vendor to do it is like pulling teeth.

What I'm really after is the best security configuration for the application which will provide the highest level of security, and more importantly please my manager :)

The only things we have control over are whether the application uses the SQL Password or Windows Authentication, and who can access the folder with the application in it.

When using the SQL Password, the application can only log into the database using the one account, which basically must have rights to do everything.

Using Window Authentication, I have to add individual users to the SQL Server to allow the application to access the database when they are using the application. However, this will allow the user to use other applications to access the server, since they have rights to it.

What do you think is the best configuration?

Thanks.

|||

Where does the application store the sa password? If your users can easily get to it, they can connect as sa. Also, if they can debug the application, they can get the password and connect directly as sa. So the drawback of having the application connect as sa is that your users could figure out the sa password and can then become admins.

If you use Windows authentication, the users could connect on their own to the server, but they would probably not be administrators (unless the application makes any Windows login that is used with it an administrator).

I'm not sure which one of these you would prefer.

Thanks
Laurentiu

Attaching a DB to a Network Folder

I'm trying to attach a db to a network folder.

I used the sproc sp_attach_db from master db to attach a test db to a local drive (c:\test) and works fine.

EXEC sp_attach_db @.dbname = N'myDatabase',
@.filename1 = N'C:\Test\myDatabase.mdf'

NO PROB. With this--

but when i try to attach a test td to a network drive, (\\computer\test) does not work.

EXEC sp_attach_db @.dbname = N'myDatabase',
@.filename1 = N'\\computer\test\myDatabase.mdf'

--I get this error message -

File '\\computer\test\myDatabase.mdf' is on a network device not supported for database files.

So i'm thinking that I can not use sp_attach_db sproc to attach a db to a network drive?

Is there any other way to attach a db to a network drive?

I even tried to map the network folder and it did not work.

EXEC sp_attach_db @.dbname = N'myDatabase',
@.filename1 = N'z:\test\myDatabase.mdf'

Yeah i forgot to tell you I'm using SQL Server 2000.
If you have ever run into this kind of situation, please let me know.

Thanks.

Irv

This link may help

http://support.microsoft.com/default.aspx?scid=kb;en-us;304261

specifically the use of trace flag 1807

|||You could try adding trace flag 1807
Allows you to configure SQL Server with network-based database files.

Note: I would not do this with a database myself because I don't know the down side.

See thread http://forums.databasejournal.com/showthread.php?t=30580&goto=nextnewest

Tim S

|||

Thanks Guys, That was really helpful!

Irv

attaching a DB in express 2005

I am having trouble attaching a db to ms sql express 2005.

I have the db in a folder deep down in my drive, within an asp.net website project. For some wierd reason i can't drill down more than two levels to get to the DB

Anyone have any ideas why?

Ilan

Hey,

Yes, there are security problems for the account that can access it (the SQL Server internal account). I've experienced this as well, and it is weird, but if you copy the DB to c:\program files\microsoft SQL Server\90\, and then into one of the data folders, it can access it there (probably can access from that root folder too).

|||

So you recommend that i copy the DB in the microsft SQL folder and once i have linked to it in express move it to the data folder for my project?

|||

Hey,

It's a minor annoyance, but it's not too bad. Yes, I have found no other thing to do in that situation... The alternatives are to attach the database to the database server, or use straight-up T-SQL to do everything you want. You can use T-SQL to manage your database, as you can use T-SQL for everything you do in the editor.