How do I verify logged in user account against SQL Sever

Question

So, I'm trying to incorporate some kind of authentication into my WPF Application.

Currently, the application connects to a SQL Server, retrieves data from the database, inserts data into the database and updating data in the database. It works as intended.

What I would like to do next is when the Application starts up it checks whether the logged in user has permission to connect to and interact with the Server - and then deny access to the application or certain parts of it.

My SQL Server is using Windows Authentication for authentication, and the users will be on the same network and domain that will be connecting to the SQL Server.

I'm not very familiar with Windows Authentication and how it would work in my Application, so any help/examples/point in the right direction would greatly be appreciated.

[EDIT]: Thanks to Jan W who pointed me in the direction of checking the username against the server in the SQL layer with IS_MEMBER, I stumbled upon HAS_DBACCESS.

With a little bit of playing around, I created this function to check if the current user has access to the Database. If not, I disable what I need to in the application or give the user a message that they have not got permission:

Private Shared Function HasDBAccess(connectionString As String) As Boolean
    Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand
    Dim reader As Data.SqlClient.SqlDataReader

    Dim conn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(connectionString)
    command.CommandText = "SELECT HAS_DBACCESS ('yourdatabasenamehere')"
    command.CommandType = Data.CommandType.Text
    command.Connection = conn

    conn.Open()

    reader = command.ExecuteReader()

    If reader.HasRows Then
        Do While reader.Read()
            Dim hasAccess As Integer = reader.GetInt32(0)
            Select Case hasAccess
                Case 1
                    Return True
                Case 0
                    Return False
            End Select
        Loop
    End If

    conn.Close()
End Function

Thanks Jan :)

score 0 · Accepted Answer · edited May 23 '17 at 12:22

0

First of all I think what you're after is to implement authorization not authentication. Authentication is already done by domain controller in your network.

Authentication versus Authorization

There is a lot of different approaches to the problem you are trying to solve depending on your business needs. I could imagine that you could have different acces roles defined as Domain Groups in your AD. Then you could check against AD if the user interacting with your application is a member of a group or not. It can be done in the application layer (How to check if a user belongs to an AD group?) or in the SQL layer (https://msdn.microsoft.com/en-us/library/ms186271.aspx)

Regards Jan

edited May 23 '17 at 12:22

Community

1
1

answered Aug 25 '16 at 13:02

Jan W

444
4
7

Thanks! The 1st Link helped me understand that better, and you are right. I'm trying to implement 'Authorization', and this from the SQL layer as the last link you provided shows. So it will just be a matter of creating a query against the database in question to check whether the current 'AUTHENTICATED' user is a User or has a role on that database? – GaaTY Aug 25 '16 at 13:17

score 0 · Answer 2 · answered Aug 25 '16 at 13:15

0

Take a look at this: WPF: Implementing Custom Authentication And Authorization

But I did it in this way: CodeProject: Implement UI Element Authorization in WPF

answered Aug 25 '16 at 13:15

Saman Hakimzadeh Abyaneh

852
3
16
31

How do I verify logged in user account against SQL Sever

2 Answers2