0

I have a simple web-service (C#,ASP.NET Core3) that exposes a few stored procs in my database.

The website is running on Windows Authentication (it's an intranet app), so I know the identity of each user that calls a method on the service and I know that the user has been authenticated.

Now, I'd like to establish the connection from the web-service to SQL using the identity of the user that made the web-request. I don't want to connect to the database as some generic user that has super-access to my db.

It's almost obvious that I would also like to use the logged in user in SQL to populate the user id field in my Audit-tables.

Is this possible at all? If anyone knows what terms I should even google for - I'd really appreciate any pointers. All my searches so far seems to return results that are way off topic. I guess, I'm missing a crucial terms for this kind of thing - or it might just not be possible...

Is there any other way to pass the userid to SQL without altering every proc in the database to accept a username? That just seems slightly ridiculous.

Mulciber Coder
  • 75
  • 5

1 Answers1

0

You have to use Trusted Connection in your connection string (See What is a Trusted Connection?)

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

You must also ensure that each user has the right to execute the stored procedures (See Grant execute permission for a user on all stored procedures in database?)

Then you can use SUSER_NAME(), SUSER_ID(), SUSER_SNAME(), SUSER_SID() in your queries.

An0d
  • 195
  • 1
  • 10
  • Thanks. I'm using Trusted Connection at the moment - but the credentials that are passed through are the credentials of the user running the web-app in IIS (which is correct AFAIK), not the user that called the service. Do you know if you can pass the calling user's id through in some way? – Mulciber Coder Oct 30 '19 at 08:50
  • Please look at this https://docs.microsoft.com/en-us/aspnet/core/security/authentication/windowsauth?view=aspnetcore-3.0&tabs=visual-studio#iisiis-express and this https://docs.microsoft.com/en-us/aspnet/core/security/authentication/windowsauth?view=aspnetcore-3.0&tabs=visual-studio#impersonation – An0d Oct 30 '19 at 09:04