_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