Trying to find the currently logged on user

  • Thread starter Thread starter Marc Scheuner [MVP ADSI]
  • Start date Start date
M

Marc Scheuner [MVP ADSI]

Folks,

I'm new to ASP.NET development accessing a SQL Server database, and I
was wondering if there's any way in ADO.NET to find out what user
account is being used to access a SQL Server database.

In my case, I have a virtual directory on my IIS server that specifies
a specific user account for anonymous access, and I'd like to be able
to find out which one is being used when I create and open a
SqlConnection to my database. Is there a "SqlConnection.CurrentUser"
property or something similar? Or do I need to use a SQL statement on
the server itself?

Thanks!
Marc
 
If you are supplying the Sql username and password in the connection string,
then it will connect using that specific user. Unless it can't connect via
TCP/IP to port 1433, in which case it will automatically switch over to named
pipes and authenticate using the account configured for ASP.NET (ASPNET by
default), unless you have impersonation configured in your web.config
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/vxconImpersonation.asp).

The best way to check is to use Enterprise Manager though.

"server=myserver;database=mydatabase;uid=mysqluser;pwd=mypassword" (example
of passing Sql user credentials in the connection string)

Hope that helped...
 
The user account, more specificallly the SQL Server login, that logs on SQL Server depends on:
a.. The authentication type you are using to connect SQL Server.
b.. The authentication type you are using in your ASP.NET application.
c.. The use of impersonation in your ASP.NET application

The authentication type used to connect SQL Server is stablished in the connection string and can be either integrated (also called Windows or trusted) or standard (also called SQL Server).
a.. The connection string for integrated security includes the attribute "integrated security=SSPI" or "Trusted = Yes".
b.. The connection string for standard authentication includes the user and password "UserID = someuser; Password=The password"

If you are using standard authentication to connect SQL Server, the login used to log on SQL Server is the one specified in the connection string.

If your ASP.NET application is using Windows authentication and you are using integrated security to connect SQL Server:
a.. If you are not using impersonation on your ASP.NET application, then the actual login that logs on SQL Server is the windows account under which credentials ASP.NET is running.
b.. If you are using impersonation on your ASP.NET application, the actual login that logs on SQL Server is the windows user that uses the ASP.NET application. This user might be the anonimous user specified for anonimous access if you allow anonimous access to your ASP.NET application.

If your ASP.NET application is not using Windows authentication and you are using integrated security to connect SQL Server:
a.. The actual loging that logs on SQL Server is the windows account under which credentials ASP.NET is running.

You can obtain the user that logged on SQL Server by runing the following T-SQL statement:

SELECT SUSER_SNAME()

You can get the user that is using your ASP.NET application by means of the following:

HttpContext.Current.User


Regards from Madrid (Spain)

Jesús López
VB MVP
Solid Quality Learning
www.solidqualitylearning.com
 
Hi Nate
If you are supplying the Sql username and password in the connection string,
Click to expand...

Nope - it's using impersonation. The connection string only contains
"integrated security=SSPI" and it will then use the Windows user
specified in the virtual directory as the one to use for anonymous
access. So I can't just parse teh connectino string to get the user.

Marc
 
Hi Jesús,
The user account, more specificallly the SQL Server login, that logs on SQL Server depends on:
a.. The authentication type you are using to connect SQL Server.
b.. The authentication type you are using in your ASP.NET application.
c.. The use of impersonation in your ASP.NET application
Click to expand...

I'm using the user defined in the IIS virtual directory for anonymous
access, which is a Windows user on my IIS machine. Authentication in
ASP.NET is set to "Windows", and impersonation is set to true. My
connection string only contains a "Integrated Security=SSPI" statement
- no explicit user name is given.

And that user (defined on the virtual directory in IIS, and used to
log onto SQL Server) is the one I'd like to be able to find. It's
*NOT* the user running the app (e.g. running the browser which
connects to my web site) - it's the user defined on the virtual
directory for anonymous access.
You can get the user that is using your ASP.NET application by means of the following:
HttpContext.Current.User
Click to expand...

This only returns an empty string, since I'm using anonymous access to
the web site..... yet, in the background, the page still uses a valid
Windows user to access the database.

Marc
 
Hi Mark,

Since you are using impersonation you can get the user by means of:

System.Security.Principal.WindowsIdentity.GetCurrent()

Regards from Madrid (Spain)

Jesús López
VB MVP
 
Hi Jesús,
Since you are using impersonation you can get the user by means of:
System.Security.Principal.WindowsIdentity.GetCurrent()
Click to expand...

Works like a charm - thanks a lot!

Marc
 
Back
Top