Using MS Sql Server to store Passwords

[leoandru]

y would u want to use the sql server usernames and password? Isnt a id an password unique to each account your creating? In other words If you were storing my jabber id and password in sql server, wasnt that id and password choosen by me? and not a usename and login for the server.
This is making no sense to me..

You can mess with any privilages I may have in your software, but let me choose my own password.. If u get what i'm saying.

[jamrock]

y would u want to use the sql server usernames and password? Isnt a id an password unique to each account your creating? In other words If you were storing my jabber id and password in sql server, wasnt that id and password choosen by me? and not a usename and login for the server.
This is making no sense to me..

I hope I am understanding your question.

Each user who logs into MS SQL server has at least two objects. The first object is a login which allows him access to SQL Server. The second object is a user account which gives him access to a specific database.

The first object is found in
security > logins

The second object is found in

database_name > users

If a user has access to 3 databases he will have a login object and 3 user objects. If he has access to 2 databases he will have a login object and 2 user objects.

This is a separate concept from the user account that the MSSQLSERVER service uses to logon.

[leoandru]

I am developing a software and I am using MS Sql Server 2000 to store user account information for each person who uses the system.

I hope I am understanding your question.

Each user who logs into MS SQL server has at least two objects. The first object is a login which allows him access to SQL Server. The second object is a user account which gives him access to a specific database.

I think your misunderstaning my question.. I don't think hes trying to set a username and password for sql server itself. Hes developing an app that requires user authentication and he is using sql server to store information abot these users, their roles and their credentials (username, and password). I was simply asking why would it be necessary or ideal that the users be assigned a username and password for the database itself, they arn't doing database admin it seems.

[jamrock]

why would it be necessary or ideal that the users be assigned a username and password for the database itself, they arn't doing database admin it seems.

MS SQL Server has two authentication methods. SQL Server authentication (Mixed mode) or Windows authentication.

With SQL Server Authentication, usernames and passwords are stored in SQL Server.

Data is stored in tables in the database. If users are not assigned a user object within the database, they cannot access the data. They will not be able to even read the data.

The process of granting a user access to a database creates the user object in that database.

MS SQL server databases include the user information. If you restore a MS SQL database, to another database or server, you will restore the user object.

This user information includes some of the user's security settings for the data.

I suspect his application will need to either create each user in MS SQL Server or use a generic account for all users. He may need to hold additonal information about the user in other tables. I am not sure how this is done.

The other option would be to configure SQL Server to use Windows Authentication. With Windows Authentication, the user is authenticated based on the role of his Windows account/group.

With this method, the user account and password are not stored in SQL Server.

Microsoft argues that Windows Authentication is more secure since the user name and password do not have to be sent across the network. Once the user is authenticated by the O/S and is authorized to access the database, he will not be prompted for a username and password.

I must emphasize that I am not a programmer. I am just someone who has used applications that have been written from the ground up to run on MS SQL Server.

[leoandru]

I think we are arguing 2 different points jamrock. your looking at it from a perspective of a database administartive task and not as a simple app that jus need to store a few user's data in a database.

Again I keep going back to an app like jabber. When u create a jabber account ur info needs to be stored somewhere. In the case of datbase storage, a jabber server itself requires only one login for the database server in use (that would be where windows login or sql server login applys). The user's account including his, id and password gets stored in the database itself. This completely unrealted to sql server logins for each user of a jabber account and you dont need a sql account to use the jabber software.

I hope you see what im getting at?

[jamrock]

I understand your point clearly. I have configured Jabber with MSSQL and OpenLDAP.

Are you familiar with MS SQL Server?

I can only tell you how it works with the applications that I have seen. Perhaps they were badly designed. Perhaps not.

I am currently working with Windows Sharepoint Services using MS SQL Server as the database backend. The user authentication logic is the same. Sharepoint is a product developed by Microsoft that runs on Windows 2003 Server and IIS.

Perhaps the MS SQL certified members of the forum could enlighten my darkness.

[samuelmitch]

When designing an application using MS Sql Server database, how is authentication done at the application layer? I would want to authenticate users for the application and not the database. What is your suggestion? as i said before I am using the database to store the users accounts information.
I am looking for any suggestions...

[seanbee20]

I think we have been missing the intentions of the poster in regards to the application he is trying to create, as leo has been saying that all that is required is a simple application authentication model which just happens to be utilizing a SQL Server database, whilst SQL Server uses mixed and windows authentication; for this purpose what is required is to store the application's users in a table with the passwords encrypted, this table as said before will store added information that SQL Server cant store using its built in security model.

What i have done when doing apps of this nature is to build my own encryption/decryption logic.

I would have a table say called users and another one called access_rights etc. When creating a new user I would simple say insert into users values ('username', encrypt(password), group no) etc.

When the statement is evaluated a function called encrypt is called to do the encryption.

My advise for you not wanting to display the password to the admin in the grid is to simply omit this field from the select statement used to populate the grid, and when it is time for the admin to create or edit a password simply display this data in a textbox with the passwordchar property set.

Here is a code segment of how I create a new user using the disconnected ADO.NET approach of using datatables.

Dim expDate As String = Today.AddDays(passExp).ToString("MM\/dd\/yyyy")

        With dbDataSet.Tables("Admin")
            Dim dr As DataRow = .NewRow
            dr("FirstName") = Trim(firstName).ToUpper
            dr("LastName") = Trim(lastName).ToUpper
            dr("groupno") = Trim(CStr(groupLevel))
            dr("expDays") = Trim(CStr(passExp))
            dr("expDate") = expDate
            dr("loginname") = Trim(username)
            dr("passin") = Encrypt(password)
            dr("delflag") = "N"
            dr("createdOn") = DateTime.Now.ToString("MM\/dd\/yyyy")
            .Rows.Add(dr)
        End With

Obviously I cant post my encryption routine for security reasons but .net has several options built in

[samuelmitch]

hey guys, thank you all for your suggestion
what i did was just use the hash function i showed you and stored it as byte in the database, i discovered that the hash function had a comparison sub function which i used to compare the byte from the sql server with the string from the program and if it does not match then it return false. it is working perfectly.