SimpleIdServer icon indicating copy to clipboard operation
SimpleIdServer copied to clipboard
verified publisher icon simpleidserver

Is it possible to integrate SQL Server

Open furkankurt1 opened this issue 3 years ago • 5 comments

Hello. I could not find the proper starting point of SQL integration. Is it possible to use this project to provision the users into SQL Database? Best regards,

furkankurt1 avatar Mar 07 '22 10:03 furkankurt1

Hello,

There are different methods to provision users into an SQL Server database :

  • Just In Time Provisioning : Automatically create new accounts in the right systems for new people when they authenticate to an external identity provider.
  • Automate provisioning : Automatically create new accounts in the right systems for new people when they join your team or organization.

Both methods and more are supported by SimpleIdServer.

Pre-requisite

Install SimpleIdServer template :

dotnet new --install SimpleIdServer.Templates

Just In Time Provisioning

An OPENID server with an SQL Connection must be configured :

A SimpleIdServer template exists to create OPENID server with EF support.

  • Execute the command line dotnet new openidef -n OpenId.
  • Create the migration scripts dotnet ef migrations add Init.
  • Update the CONNECTIONSTRING parameter in the files OpenIDMigration.cs and Startup.cs before running the solution.

Example project : https://github.com/simpleidserver/SimpleIdServer/tree/master/src/OpenID/SimpleIdServer.OpenID.SqlServer.Startup

Automate provisioning

A SCIM2.0 endpoint with an SQL Connection must be configured :

A SimpleIdServer template exists to create SCIM server with EF support.

  • Execute the command line dotnet new scimef -n ScimHost.
  • Create the migration scripts dotnet ef migrations add Init.
  • Update the CONNECTIONSTRING parameter in the files ScimMigration.cs and Startup.cs before running the solution.

Example project : https://github.com/simpleidserver/SimpleIdServer/tree/master/samples/UseSCIMSqlServer

simpleidserver avatar Mar 07 '22 12:03 simpleidserver

Can this "Automate Provisioning" be integrated into AzureAD so if a user gets added to a group or directory, the synchronization will be done? Thank you for your explanation in detail.

furkankurt1 avatar Mar 07 '22 16:03 furkankurt1

Hello,

Indeed the automatic provisioning can be configured between Azure AD and our SCIM server.

To do so, please follow the steps below :

  • A SimpleIdServer SCIM server must be deployed.
  • Configure Microsoft Azure Active Directory to enable automatic provisioning of users between SCIM and Azure AD. For more information please refer to this documentation : https://docs.microsoft.com/en-us/azure/active-directory/app-provisioning/use-scim-to-provision-users-and-groups.
  • When a representation is added in SCIM (for example : User, Group etc...). A client can be developed to subscribe to the messages received from a queue. When a message like "representation-added" or "representation-updated" is received, an API operation can be called to create or update user in your preferred OPENID server. SimpleIdServer offers some APIS to facilitate the user management.

The schema below shows the interactions between the different components :

image

simpleidserver avatar Mar 07 '22 21:03 simpleidserver

Thank you for this great explanation. I followed the instructions till

"When a representation is added in SCIM (for example : User, Group etc...). A client can be developed to subscribe to the messages received from a queue. When a message like "representation-added" or "representation-updated" is received, an API operation can be called to create or update user in your preferred OPENID server. SimpleIdServer offers some APIS to facilitate the user management."

Could you please give more detail about subscribing to the messages, and the ongoing process? What do you mean by 'OpenID Server'? Do I have to combine 'OpenID' system to secure the application by following this part of the documentation? https://simpleidserver.github.io/SimpleIdServer/documentation/openid/installation.html

[cid:8022c46a-3f4a-432c-9d5d-27d5031b1a7f]

Thank you, Best regards.

From: SimpleIdServer @.> Sent: Monday, March 7, 2022 22:17 To: simpleidserver/SimpleIdServer @.> Cc: Furkan Kurt @.>; Author @.> Subject: Re: [simpleidserver/SimpleIdServer] Is it possible to integrate SQL Server (Issue #228)

Hello,

Indeed the automatic provisioning can be configured between Azure AD and our SCIM server.

To do so, please follow the steps below :

  • A SimpleIdServer SCIM server must be deployed.
  • Configure Microsoft Azure Active Directory to enable automatic provisioning of users between SCIM and Azure AD. For more information please refer to this documentation : https://docs.microsoft.com/en-us/azure/active-directory/app-provisioning/use-scim-to-provision-users-and-groups.
  • When a representation is added in SCIM (for example : User, Group etc...). A client can be developed to subscribe to the messages received from a queue. When a message like "representation-added" or "representation-updated" is received, an API operation can be called to create or update user in your preferred OPENID server. SimpleIdServer offers some APIS to facilitate the user management.

The schema below shows the interactions between the different components :

[image]https://user-images.githubusercontent.com/10213388/157119278-ee6e1cde-0ab7-487e-8d77-485a0638a38a.png

— Reply to this email directly, view it on GitHubhttps://github.com/simpleidserver/SimpleIdServer/issues/228#issuecomment-1061145443, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ANDJ4YJRAR47KYIMFL5M37LU6ZW7HANCNFSM5QC2TVBA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub. You are receiving this because you authored the thread.Message ID: @.***>

furkankurt1 avatar Mar 07 '22 23:03 furkankurt1

Hello,

When a message is received from the message broker (for examples "RepresentationAdded" or "RepresentationUpdated"). You can decide to create a user in an Identity server like OPENID, SAML IDP or softwares like Slack, gitlab etc... It's not mandatory to create a user in an OPENID server, it depends on your use case :).

If the scenario you want to support is to add a new user in the OPENID server then you can download the ZIP file and run the projects :

dotnet run --urls=http://localhost:5001
  • Open a command prompt and execute this command under the SCIM folder :
dotnet run --urls=http://localhost:5002
  • When everything is up & running you can execute the request present in the POSTMAN collection. It contains two requests: ** First request : Get an access token. ** Second request : Add a user.

At the end, a new user should be added in the OPENID database :)

Kind Regards,

SimpleIdServer

simpleidserver avatar Mar 08 '22 15:03 simpleidserver