0

I have access to a SQL Server DB system that only allowed "Windows Authentication" - that is, use your windows login credential to access the data. (Not using a username-password). It also controls database permissions.

I want to build a REST API for users not familiar with SQL to get data from that server. The problem is, I need to take user's credential to execute the command, not the machine that hosts the REST API. How could I design the system that allow user to pass their login and let the API send query to the server using that specific login?

I found a reference from Microsoft, not sure if it's relevant.

https://learn.microsoft.com/en-us/sql/relational-databases/security/choose-an-authentication-mode?view=sql-server-2017

jarlh
  • 42,561
  • 8
  • 45
  • 63
MTANG
  • 508
  • 5
  • 16
  • 1
    Mechanism that allows this is called Kerberos delegation. And no, SQL Server is just a data source in this scenario, you wouldn't have to introduce any changes into its configuration (apart from granting permissions to corresponding AD users, that is). – Roger Wolf Nov 16 '18 at 00:31
  • Hi @RogerWolf. Thanks for your suggestion. Could you please give me a brief framework/guideline how I could achieve this? – MTANG Nov 16 '18 at 14:37

1 Answers1

0

What you are after is Kerberos delegation, sometimes also known as "double hop". It is not something you have to explicitly implement in your code, as long as you stick with the Microsoft stack; rather, it is a matter of configuration.

This post describes the setup required for the feature to work in sufficient details. You can also find some basic requirements in this help article. For anything beyond that, just google it.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
  • I'm using Python (django) + SQL Server on Windows machine. Do you think it's easy or need some careful configuration? Thanks. – MTANG Nov 21 '18 at 14:27
  • @MTANG, there might be some additional things you need to do / add in your code, not sure which ones though - I'm not really a web dev. – Roger Wolf Nov 22 '18 at 00:19