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

Question

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

Answer 1

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')  

Answer 2

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

Answer 3

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.