how to connect to sql server using integrated security asp.net

HOUSSEM MAHJOUBI 286 Reputation points
2022-09-12T14:11:46.49+00:00

Hi members
i want connect to sql server using integrated security
the idea is that i can get the username of who using the website in sql server
this is the connection Dim stupremat As New SqlConnection("server=192.168.2.200;database='" & DropDownList2.SelectedItem.Text & "'; Integrated Security=SSPI")
and the query i use to get the username is

DECLARE @USER _op varchar(50)
DECLARE @apl _op smalldatetime
DECLARE @ref_ahref varchar(19)
DECLARE @ref1 varchar(19)
DECLARE @dp_NoPrincipal numeric
DECLARE @dp_NoPrincipal1 numeric
DECLARE @de_No numeric
DECLARE @de_No1 numeric
DECLARE @ancien_empl varchar(19)
DECLARE @nouveau_empl varchar(19) 

				SELECT @user_op = hostname , @Date_op = getdate() FROM master..sysprocesses WHERE spid=@@SPID  --nt_username

please help me

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,199 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,777 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2022-09-12T17:12:59.667+00:00

    Websites generally use a single login for ALL website users. You do not want to create a login for every web user in SQL Server.

    The solution to your problem is to use the SESSION_CONTEXT to store the web user before calling SQL Server. Then you can retrieve it in your code.

    See:
    https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-session-context-transact-sql?view=sql-server-ver16

    Before every SQL call, run (yes, you need to do this before every call because the connection may be pooled): 

    EXEC sys.sp_set_session_context @key = N'UserName', @value = '{This is the user name}';

    Then in your SQL Code you use: 

    SESSION_CONTEXT(N'UserName')
    2 people found this answer helpful.
  2. HOUSSEM MAHJOUBI 286 Reputation points
    2022-09-12T14:22:34.61+00:00

    i don't know if i make it using integrated security it will give me the username or not
    if not any idea that can give me the username
    cause now i use the query that has user and password after in client side if i look always get the username as a server nothing else
    and of i use other application like sage commercial it give me the username
    please help

    0 comments
  3. Michael Taylor 48,656 Reputation points
    2022-09-12T14:54:41.857+00:00

    If you use Integrated Security=SSPI in the connection string then it'll use the credentials of the process that is calling SQL. For a website you'd also need to enable the site to use Windows auth otherwise you'd get the app pool identity.

    Irrelevant, to get the current user name for the connected request you can use SUSER_SNAME. This is the traditional way of getting the user. It will be the Windows user if the connection is Integrated Security or the SQL user if you're using a dedicated user account. Note that there are other variants as well. For example SESSION_USER returns something similar. But in my experience SUSER_SNAME would be what you wanted to use if you needed to capture the user name for the connection running the code. It is commonly used for auditing.