This version of the Ed-Fi ODS / API is no longer supported. See the Ed-Fi Technology Version Index for a link to the latest version.
_How To: Manage Keys and Secrets
The SQL queries on this page enable a database administrator to view information about security configurations. Execute the queries from the EdFi_Admin database.
Create a Key and Secret
To create a key/secret pair for a vendor and link it to a local education agency, run the script below in the desired environment.
You should replace the values for @VendorName
, @FullName
, @EmailAddress
, and @LocalEducationAgencyId
with appropriate values. The @ClaimSetName
value will need to be replaced with a valid ClaimSetName from the EdFi_Security database.
USE EdFi_Admin GO DECLARE @VendorName varchar(150) DECLARE @VendorId int DECLARE @UserId int DECLARE @ApplicationId int DECLARE @FullName varchar(150) DECLARE @EmailAddress varchar(150) DECLARE @LocalEducationAgencyId int DECLARE @ApplicationLocalEducationAgencyId int DECLARE @ClaimSetName nvarchar(255) SET @VendorName = 'vendor name' SET @FullName = 'User' SET @EmailAddress = 'user@vendor.com' SET @LocalEducationAgencyId = '' SET @ClaimSetName = '' SELECT @VendorId = VendorId FROM Vendors WHERE VendorName = @VendorName IF (@VendorId IS NULL) BEGIN INSERT INTO Vendors (VendorName) VALUES (@VendorName) SET @VendorId = @@IDENTITY INSERT INTO Users (Email, FullName, Vendor_VendorId) VALUES (@EmailAddress, @FullName, @VendorId) SET @UserId = @@IDENTITY INSERT INTO Applications (ApplicationName, Vendor_VendorId, ClaimSetName) VALUES (@VendorName, @VendorId, @ClaimSetName) SET @ApplicationId = @@IDENTITY END ELSE BEGIN SELECT TOP 1 @UserId = UserId FROM Users WHERE Vendor_VendorId = @VendorId SELECT TOP 1 @ApplicationId = ApplicationId FROM Applications WHERE Vendor_VendorId = @VendorId END PRINT "Vendor Id: " @VendorId PRINT @ApplicationId PRINT @UserId INSERT INTO ApplicationLocalEducationAgencies (LocalEducationAgencyId, Application_ApplicationId) VALUES (@LocalEducationAgencyId, @ApplicationId) SET @ApplicationLocalEducationAgencyId = @@IDENTITY DECLARE @Key varchar(20) DECLARE @Secret varchar(15) DECLARE @ApiClientId int SET @Key = REPLACE(SUBSTRING(CAST(NEWID() AS varchar(50)), 0, 20), '-', '') SET @Secret = REPLACE(SUBSTRING(CAST(NEWID() AS varchar(50)), 0, 15), '-', '') INSERT INTO ApiClients ([Key], Secret, Name, IsApproved, UseSandbox, SandboxType, Application_ApplicationId, User_UserId) VALUES (@Key, @Secret, @VendorName, 1, 0, 0, @ApplicationId, @UserId) SET @ApiClientId = @@IDENTITY INSERT INTO ApiClientApplicationLocalEducationAgencies (ApplicationLocalEducationAgency_ApplicationLocalEducationAgencyId, ApiClient_ApiClientId) VALUES (@ApplicationLocalEducationAgencyId, @ApiClientId) PRINT @Key PRINT @Secret
View a List of Keys, Secrets, and Local Education Agencies
To see the full list of keys, secrets, and LEAs, use the SELECT statement below.
USE EdFi_Admin GO SELECT Vendors.VendorName, Applications.ApplicationName, ApiClients.[Key], ApiClients.Secret, ApplicationLocalEducationAgencies.LocalEducationAgencyId FROM Vendors JOIN Applications ON Applications.Vendor_VendorId = Vendors.VendorId JOIN ApiClients ON ApiClients.Application_ApplicationId = Applications.ApplicationId JOIN ApiClientApplicationLocalEducationAgencies ON ApiClients.ApiClientId = ApiClientApplicationLocalEducationAgencies.ApiClient_ApiClientId JOIN ApplicationLocalEducationAgencies ON ApplicationLocalEducationAgencies.ApplicationLocalEducationAgencyId = ApiClientApplicationLocalEducationAgencies.ApplicationLocalEducationAgency_ApplicationLocalEducationAgencyId