/// <summary>
/// The non-boilerplated test code of the APIs for managing the lifecycle of a given database's data masking policy. It is meant to be called with a name of an already exisiting database (and therefore already existing
/// server and resource group). This test does not create these resources and does not remove them.
/// </summary>
private void TestDataMaskingPolicyAPIs(SqlManagementClient sqlClient, string resourceGroupName, Server server, Database database)
{
DataMaskingPolicyGetResponse getDefaultPolicyResponse = sqlClient.DataMasking.GetPolicy(resourceGroupName, server.Name, database.Name);
DataMaskingPolicyProperties properties = getDefaultPolicyResponse.DataMaskingPolicy.Properties;
// Verify that the initial Get request contains the default policy.
TestUtilities.ValidateOperationResponse(getDefaultPolicyResponse, HttpStatusCode.OK);
VerifyDataMaskingPolicyInformation(MakeDefaultDataMaskingPolicyProperties(), properties);
// Modify the policy properties, send and receive, see it its still ok
properties.DataMaskingState = "Enabled";
properties.ExemptPrincipals = "principal1;principal2";
DataMaskingPolicyCreateOrUpdateParameters updateParams = new DataMaskingPolicyCreateOrUpdateParameters();
updateParams.Properties = properties;
var updateResponse = sqlClient.DataMasking.CreateOrUpdatePolicy(resourceGroupName, server.Name, database.Name, updateParams);
// Verify that the initial Get request of contains the default policy.
TestUtilities.ValidateOperationResponse(updateResponse, HttpStatusCode.OK);
DataMaskingPolicyGetResponse getUpdatedPolicyResponse = sqlClient.DataMasking.GetPolicy(resourceGroupName, server.Name, database.Name);
DataMaskingPolicyProperties updatedProperties = getUpdatedPolicyResponse.DataMaskingPolicy.Properties;
// Verify that the Get request contains the updated policy.
TestUtilities.ValidateOperationResponse(getUpdatedPolicyResponse, HttpStatusCode.OK);
VerifyDataMaskingPolicyInformation(properties, updatedProperties);
}
/// <summary>
/// The non-boilerplated test code of the APIs for managing the lifecycle of a given database's security alert policy. It is meant to be called with a name of an already existing database (and therefore already existing
/// server and resource group). This test does not create these resources and does not remove them.
/// </summary>
private static void TestDatabaseSecurityAlertApis(SqlManagementClient sqlClient, string resourceGroupName, Server server, Database database)
{
var getDefaultDatabaseSecurityAlertPolicyResponse = sqlClient.SecurityAlertPolicy.GetDatabaseSecurityAlertPolicy(resourceGroupName, server.Name, database.Name);
var properties = getDefaultDatabaseSecurityAlertPolicyResponse.SecurityAlertPolicy.Properties;
// Verify that the initial Get request contains the default policy.
TestUtilities.ValidateOperationResponse(getDefaultDatabaseSecurityAlertPolicyResponse);
VerifySecurityAlertPolicyInformation(GetDefaultDatabaseSecurityAlertProperties(), properties);
// Modify the policy properties, send and receive, see it its still ok
ChangeSecurityAlertPolicy(properties);
var updateParams = new DatabaseSecurityAlertPolicyCreateOrUpdateParameters { Properties = properties };
var updateResponse = sqlClient.SecurityAlertPolicy.CreateOrUpdateDatabaseSecurityAlertPolicy(resourceGroupName, server.Name, database.Name, updateParams);
// Verify that the initial Get request contains the default policy.
TestUtilities.ValidateOperationResponse(updateResponse);
var getUpdatedPolicyResponse = sqlClient.SecurityAlertPolicy.GetDatabaseSecurityAlertPolicy(resourceGroupName, server.Name, database.Name);
var updatedProperties = getUpdatedPolicyResponse.SecurityAlertPolicy.Properties;
// Verify that the Get request contains the updated policy.
TestUtilities.ValidateOperationResponse(getUpdatedPolicyResponse);
VerifySecurityAlertPolicyInformation(properties, updatedProperties);
}
/// <summary>
/// The non-boilerplated test code of the APIs for managing the lifecycle of a given database's auditing policy. It is meant to be called with a name of an already existing database (and therefore already existing
/// server and resource group). This test does not create these resources and does not remove them.
/// </summary>
private void TestDatabaseAuditingAPIs(SqlManagementClient sqlClient, string resourceGroupName, Server server, Database database)
{
DatabaseAuditingPolicyGetResponse getDefaultDatabasePolicyResponse = sqlClient.AuditingPolicy.GetDatabasePolicy(resourceGroupName, server.Name, database.Name);
DatabaseAuditingPolicyProperties properties = getDefaultDatabasePolicyResponse.AuditingPolicy.Properties;
// Verify that the initial Get request contains the default policy.
TestUtilities.ValidateOperationResponse(getDefaultDatabasePolicyResponse, HttpStatusCode.OK);
VerifyDatabaseAuditingPolicyInformation(GetDefaultDatabaseAuditProperties(), properties);
// Modify the policy properties, send and receive, see it its still ok
ChangeDataBaseAuditPolicy(properties);
DatabaseAuditingPolicyCreateOrUpdateParameters updateParams =
new DatabaseAuditingPolicyCreateOrUpdateParameters {Properties = properties};
var updateResponse = sqlClient.AuditingPolicy.CreateOrUpdateDatabasePolicy(resourceGroupName, server.Name, database.Name, updateParams);
// Verify that the initial Get request contains the default policy.
TestUtilities.ValidateOperationResponse(updateResponse, HttpStatusCode.OK);
DatabaseAuditingPolicyGetResponse getUpdatedPolicyResponse = sqlClient.AuditingPolicy.GetDatabasePolicy(resourceGroupName, server.Name, database.Name);
DatabaseAuditingPolicyProperties updatedProperties = getUpdatedPolicyResponse.AuditingPolicy.Properties;
// Verify that the Get request contains the updated policy.
TestUtilities.ValidateOperationResponse(getUpdatedPolicyResponse, HttpStatusCode.OK);
VerifyDatabaseAuditingPolicyInformation(properties, updatedProperties);
}
/// <summary>
/// Create Azure Sql Server SQL2X Queue database
/// </summary>
/// <returns>MVC View</returns>
//[HttpGet("CreateDatabase")]
public ActionResult CreateDatabase()
{
if (IsLocal)
{
DatabaseCreate("NorSolutionPim");
}
else
{
SqlManagementClient sqlClient = AzureSqlClient();
var newDb = new Microsoft.Azure.Management.Sql.Models.Database {
Location = "East US 2",
LicenseType = "BasePrice",
Sku = new Sku(name: "Basic")
};
sqlClient.Databases.CreateOrUpdate(
resourceGroupName: "Default-SQL-EastUS2",
serverName: "sqlServerName",
databaseName: "NorSolutionPim",
parameters: newDb
);
}
return(View());
}
/// <summary>
/// The non-boilerplated test code of the APIs for managing the lifecycle of a given database's secure connection policy.
/// It is meant to be called with a name of an already exisiting database (and therefore already existing server and resource group).
/// </summary>
private void TestSecureConnectionAPIs(SqlManagementClient sqlClient, string resourceGroupName, Server server, Database database)
{
DatabaseSecureConnectionPolicyGetResponse getDefaultSecureConnectionPolicyResponse = sqlClient.SecureConnection.GetDatabasePolicy(resourceGroupName, server.Name, database.Name);
DatabaseSecureConnectionPolicyProperties properties = getDefaultSecureConnectionPolicyResponse.SecureConnectionPolicy.Properties;
// Verify that the initial Get request contains the default policy.
TestUtilities.ValidateOperationResponse(getDefaultSecureConnectionPolicyResponse, HttpStatusCode.OK);
VerifySecureConnectionPolicyInformation(getDefaultSecureConnectionPolicyProperties(server.Name), properties);
// Modify the policy properties, send and receive, see it its still ok
properties.SecurityEnabledAccess = "Required";
DatabaseSecureConnectionPolicyCreateOrUpdateParameters updateParams = new DatabaseSecureConnectionPolicyCreateOrUpdateParameters();
updateParams.Properties = ConvertToSecureConnectionPolicyCreateProperties(properties);
var updateResponse = sqlClient.SecureConnection.CreateOrUpdateDatabasePolicy(resourceGroupName, server.Name, database.Name, updateParams);
// Verify that the initial Get request of contains the default policy.
TestUtilities.ValidateOperationResponse(updateResponse, HttpStatusCode.OK);
DatabaseSecureConnectionPolicyGetResponse getUpdatedPolicyResponse = sqlClient.SecureConnection.GetDatabasePolicy(resourceGroupName, server.Name, database.Name);
DatabaseSecureConnectionPolicyProperties updatedProperties = getUpdatedPolicyResponse.SecureConnectionPolicy.Properties;
// Verify that the Get request contains the updated policy.
TestUtilities.ValidateOperationResponse(getUpdatedPolicyResponse, HttpStatusCode.OK);
VerifySecureConnectionPolicyInformation(properties, updatedProperties);
}
/// <summary>
/// Remove Azure SQL server SQL2X Queue database
/// </summary>
/// <returns>MVC View</returns>
//[HttpGet("RemoveDatabase")]
public ActionResult RemoveDatabase()
{
if (IsLocal)
{
DatabaseDelete("NorSolutionPim");
}
else
{
SqlManagementClient sqlClient = AzureSqlClient();
var newDb = new Microsoft.Azure.Management.Sql.Models.Database {
Location = "East US 2"
};
sqlClient.Databases.Delete(
resourceGroupName: "Default-SQL-EastUS2",
serverName: "sqlServerName",
databaseName: "NorSolutionPim"
);
}
return(View());
}
/// <summary>
/// The non-boilerplated test code of the APIs for managing the lifecycle of a given database's blob auditing policy. It is meant to be called with a name of an already existing database (and therefore already existing
/// server and resource group). This test does not create these resources and does not remove them.
/// </summary>
private void TestDatabaseAuditingApis(SqlManagementClient sqlClient, string resourceGroupName, Server server, Database database)
{
BlobAuditingGetResponse getDefaultDatabasePolicyResponse = sqlClient.BlobAuditing.GetDatabaseBlobAuditingPolicy(resourceGroupName, server.Name, database.Name);
var properties = getDefaultDatabasePolicyResponse.AuditingPolicy.Properties;
// Verify that the initial Get request contains the default policy.
TestUtilities.ValidateOperationResponse(getDefaultDatabasePolicyResponse);
VerifyAuditingPolicyInformation(GetDefaultBlobAuditProperties(), properties);
// Modify the policy properties, send and receive, see it its still ok
var updateParams = new BlobAuditingCreateOrUpdateParameters { Properties = properties };
var updateResponse = sqlClient.BlobAuditing.CreateOrUpdateDatabasePolicy(resourceGroupName, server.Name, database.Name, updateParams);
// Verify that the initial Get request contains the default policy.
TestUtilities.ValidateOperationResponse(updateResponse);
var getUpdatedPolicyResponse = sqlClient.BlobAuditing.GetDatabaseBlobAuditingPolicy(resourceGroupName, server.Name, database.Name);
var updatedProperties = getUpdatedPolicyResponse.AuditingPolicy.Properties;
// Verify that the Get request contains the updated policy.
TestUtilities.ValidateOperationResponse(getUpdatedPolicyResponse);
VerifyAuditingPolicyInformation(properties, updatedProperties);
}
/// <summary>
/// The non-boilerplated test code of the APIs for managing the lifecycle data masking rules.
/// It is meant to be called with a name of an already exisiting database (and therefore already existing server and resource group).
/// This test does not create these resources and does not remove them.
/// The flow is:
/// 1) Create policy (it's a prereq)
/// 2) Create rule1, validate its creation and its content by doing another GET call
/// 3) Update rule1, validate the update by doing another GET call
/// 4) Create rule2, validate its creation and its content
/// 5) Get the list of rules, see that there are two and each one of them has the right content
/// 6) Delete rule1, see that we get OK
/// 8) List the rules, see that we now have one rule there and it is rule 2
/// </summary>
/// <param name="sqlClient">The sqlClient</param>
/// <param name="resourceGroupName">The resource group name to use in this test</param>
/// <param name="server">The server to use in this test</param>
/// <param name="database">The database to use in this test</param>
private void TestDataMaskingRuleAPIs(SqlManagementClient sqlClient, string resourceGroupName, Server server, Database database)
{
DataMaskingPolicyCreateOrUpdateParameters policyParams = new DataMaskingPolicyCreateOrUpdateParameters();
policyParams.Properties = MakeDefaultDataMaskingPolicyProperties();
policyParams.Properties.DataMaskingState = "Enabled";
sqlClient.DataMasking.CreateOrUpdatePolicy(resourceGroupName, server.Name, database.Name, policyParams);
int ruleCounter = 1;
DataMaskingRuleCreateOrUpdateParameters ruleParams = new DataMaskingRuleCreateOrUpdateParameters();
string serverName = server.Properties.FullyQualifiedDomainName;
string uid = server.Properties.AdministratorLogin;
string pwd = server.Properties.AdministratorLoginPassword;
string dbName = database.Name;
string connString = string.Format("Server={0};uid={1}; pwd={2};Database={3};Integrated Security=False;", serverName, uid, pwd, dbName);
var conn = new SqlConnection();
conn.ConnectionString = connString;
string tableName = "table1", columnName = "column1";
string firewallRuleName = TestUtilities.GenerateName("all");
string startIp1 = "1.1.1.1";
string endIp1 = "255.255.255.255";
sqlClient.FirewallRules.CreateOrUpdate(resourceGroupName, serverName.Split('.').ElementAt(0), firewallRuleName, new FirewallRuleCreateOrUpdateParameters()
{
Properties = new FirewallRuleCreateOrUpdateProperties()
{
StartIpAddress = startIp1,
EndIpAddress = endIp1,
}
});
CreateDatabaseContents(conn, tableName, columnName);
ruleParams.Properties = MakeRuleProperties(ruleCounter++ ,tableName, columnName);
string rule1Name = ruleParams.Properties.Id;
var createRuleResponse = sqlClient.DataMasking.CreateOrUpdateRule(resourceGroupName, server.Name, database.Name, rule1Name, ruleParams);
// Verify that the initial create request went well.
TestUtilities.ValidateOperationResponse(createRuleResponse, HttpStatusCode.OK);
var getAfterCreateResponse = sqlClient.DataMasking.GetRule(resourceGroupName, server.Name, database.Name, rule1Name);
TestUtilities.ValidateOperationResponse(getAfterCreateResponse, HttpStatusCode.OK);
VerifyDataMaskingRuleInformation(getAfterCreateResponse.DataMaskingRule.Properties, ruleParams.Properties);
// Modify the policy properties, send and receive, see it its still ok
string updatedTableName = "tbl2";
CreateDatabaseContents(conn, updatedTableName, columnName);
ruleParams.Properties.TableName = updatedTableName;
var updateResponse = sqlClient.DataMasking.CreateOrUpdateRule(resourceGroupName, server.Name, database.Name, rule1Name, ruleParams);
TestUtilities.ValidateOperationResponse(updateResponse, HttpStatusCode.OK);
var getAfterUpdateResponse = sqlClient.DataMasking.GetRule(resourceGroupName, server.Name, database.Name, rule1Name);
TestUtilities.ValidateOperationResponse(getAfterUpdateResponse, HttpStatusCode.OK);
VerifyDataMaskingRuleInformation(getAfterUpdateResponse.DataMaskingRule.Properties, ruleParams.Properties);
DataMaskingRuleCreateOrUpdateParameters ruleParams2 = new DataMaskingRuleCreateOrUpdateParameters();
tableName = "table2";
columnName = "column2";
CreateDatabaseContents(conn, tableName, columnName);
ruleParams2.Properties = MakeRuleProperties(ruleCounter++, tableName, columnName);
string rule2Name = ruleParams2.Properties.Id;
createRuleResponse = sqlClient.DataMasking.CreateOrUpdateRule(resourceGroupName, server.Name, database.Name, rule2Name, ruleParams2);
TestUtilities.ValidateOperationResponse(updateResponse, HttpStatusCode.OK);
DataMaskingRuleListResponse listResponse = sqlClient.DataMasking.List(resourceGroupName, server.Name, database.Name);
TestUtilities.ValidateOperationResponse(listResponse, HttpStatusCode.OK);
Assert.Equal(2, listResponse.DataMaskingRules.Count);
VerifyDataMaskingRuleInformation(listResponse.DataMaskingRules.FirstOrDefault(r => r.Properties.Id == rule1Name).Properties, ruleParams.Properties);
VerifyDataMaskingRuleInformation(listResponse.DataMaskingRules.FirstOrDefault(r => r.Properties.Id == rule2Name).Properties, ruleParams2.Properties);
AzureOperationResponse deleteResponse = sqlClient.DataMasking.Delete(resourceGroupName, server.Name, database.Name, rule1Name);
TestUtilities.ValidateOperationResponse(deleteResponse, HttpStatusCode.OK);
DataMaskingRuleListResponse listAfterDeleteResponse = sqlClient.DataMasking.List(resourceGroupName, server.Name, database.Name);
TestUtilities.ValidateOperationResponse(listAfterDeleteResponse, HttpStatusCode.OK);
Assert.Equal(listAfterDeleteResponse.DataMaskingRules.Count, 1);
VerifyDataMaskingRuleInformation(listResponse.DataMaskingRules.FirstOrDefault(r => r.Properties.Id == rule2Name).Properties, ruleParams2.Properties);
}
/// <summary>
/// Verify that the Database object matches the provided values
/// </summary>
/// <param name="serverLocation">The expected server location</param>
/// <param name="databaseCollation">The expected database collation</param>
/// <param name="databaseEdition">The expected database edition</param>
/// <param name="databaseMaxSize">The expected database max size</param>
/// <param name="currentSlo">The expected database service objective</param>
/// <param name="db">The actual database object</param>
private static void VerifyDatabaseInformation(Database db, string serverLocation, string databaseCollation, string databaseEdition, long databaseMaxSize, Guid requestedSlo, Guid currentSlo, string status = null)
{
Assert.Equal(serverLocation, db.Location);
Assert.Equal(databaseCollation, db.Properties.Collation);
Assert.Equal(databaseEdition, db.Properties.Edition);
Assert.Equal(databaseMaxSize, db.Properties.MaxSizeBytes);
Assert.Equal(requestedSlo.ToString(), db.Properties.RequestedServiceObjectiveId);
Assert.Equal(currentSlo.ToString(), db.Properties.CurrentServiceObjectiveId);
if(status != null)
{
Assert.Equal(status, db.Properties.Status);
}
}
/// <summary>
/// The non-boilerplated test code of the APIs for managing the lifecycle data masking rules.
/// It is meant to be called with a name of an already exisiting database (and therefore already existing server and resource group).
/// This test does not create these resources and does not remove them.
/// The flow is:
/// 1) Create policy (it's a prereq)
/// 2) Create rule1, validate its creation and its content by doing another GET call
/// 3) Update rule1, validate the update by doing another GET call
/// 4) Create rule2, validate its creation and its content
/// 5) Get the list of rules, see that there are two and each one of them has the right content
/// 6) Delete rule1, see that we get OK
/// 8) List the rules, see that we now have one rule there and it is rule 2
/// </summary>
/// <param name="sqlClient">The sqlClient</param>
/// <param name="resourceGroupName">The resource group name to use in this test</param>
/// <param name="server">The server to use in this test</param>
/// <param name="database">The database to use in this test</param>
private void TestDataMaskingRuleAPIs(SqlManagementClient sqlClient, string resourceGroupName, Server server, Database database)
{
DataMaskingPolicyCreateOrUpdateParameters policyParams = new DataMaskingPolicyCreateOrUpdateParameters();
policyParams.Properties = MakeDefaultDataMaskingPolicyProperties();
policyParams.Properties.DataMaskingState = "Enabled";
sqlClient.DataMasking.CreateOrUpdatePolicy(resourceGroupName, server.Name, database.Name, policyParams);
int ruleCounter = 1;
DataMaskingRuleCreateOrUpdateParameters ruleParams = new DataMaskingRuleCreateOrUpdateParameters();
string serverName = server.Properties.FullyQualifiedDomainName;
string uid = server.Properties.AdministratorLogin;
string pwd = server.Properties.AdministratorLoginPassword;
string dbName = database.Name;
string connString = string.Format("Server={0};uid={1}; pwd={2};Database={3};Integrated Security=False;", serverName, uid, pwd, dbName);
var conn = new SqlConnection();
conn.ConnectionString = connString;
string tableName = "table1", columnName = "column1";
string firewallRuleName = TestUtilities.GenerateName("all");
string startIp1 = "1.1.1.1";
string endIp1 = "255.255.255.255";
sqlClient.FirewallRules.CreateOrUpdate(resourceGroupName, serverName.Split('.').ElementAt(0), firewallRuleName, new FirewallRuleCreateOrUpdateParameters()
{
Properties = new FirewallRuleCreateOrUpdateProperties()
{
StartIpAddress = startIp1,
EndIpAddress = endIp1,
}
});
CreateDatabaseContents(conn, tableName, columnName);
Func<DataMaskingRuleCreateOrUpdateParameters, Func<DataMaskingRule, bool>> isRuleOnColumn = (DataMaskingRuleCreateOrUpdateParameters parms) =>
{
return (DataMaskingRule r1) =>
{
return parms.Properties.ColumnName == r1.Properties.ColumnName &&
parms.Properties.TableName == r1.Properties.TableName &&
parms.Properties.SchemaName == r1.Properties.SchemaName;
};
};
ruleParams.Properties = MakeRuleProperties(ruleCounter++ ,tableName, columnName);
string rule1Name = ruleParams.Properties.Id;
var createRuleResponse = sqlClient.DataMasking.CreateOrUpdateRule(resourceGroupName, server.Name, database.Name, rule1Name, ruleParams);
TestUtilities.ValidateOperationResponse(createRuleResponse, HttpStatusCode.OK);
var listAfterCreateResponse = sqlClient.DataMasking.List(resourceGroupName, server.Name, database.Name);
TestUtilities.ValidateOperationResponse(listAfterCreateResponse, HttpStatusCode.OK);
Assert.Equal(1, listAfterCreateResponse.DataMaskingRules.Count);
DataMaskingRule receivedRule = listAfterCreateResponse.DataMaskingRules.FirstOrDefault(isRuleOnColumn(ruleParams));
VerifyDataMaskingRuleInformation(receivedRule.Properties, ruleParams.Properties);
// Modify the policy properties, send and receive, see it its still ok
ruleParams.Properties.PrefixSize = "2";
ruleParams.Properties.ReplacementString = "ABC";
ruleParams.Properties.SuffixSize = "1";
var updateRuleResponse = sqlClient.DataMasking.CreateOrUpdateRule(resourceGroupName, server.Name, database.Name, rule1Name, ruleParams);
TestUtilities.ValidateOperationResponse(updateRuleResponse, HttpStatusCode.OK);
var listUpdateResponse = sqlClient.DataMasking.List(resourceGroupName, server.Name, database.Name);
TestUtilities.ValidateOperationResponse(listUpdateResponse, HttpStatusCode.OK);
Assert.Equal(1, listUpdateResponse.DataMaskingRules.Count);
var updatedRule = listUpdateResponse.DataMaskingRules.FirstOrDefault(isRuleOnColumn(ruleParams));
VerifyDataMaskingRuleInformation(updatedRule.Properties, ruleParams.Properties);
DataMaskingRuleCreateOrUpdateParameters ruleParams2 = new DataMaskingRuleCreateOrUpdateParameters();
tableName = "table2";
columnName = "column2";
CreateDatabaseContents(conn, tableName, columnName);
ruleParams2.Properties = MakeRuleProperties(ruleCounter++, tableName, columnName);
string rule2Name = ruleParams2.Properties.Id;
var createSecondRuleResponse = sqlClient.DataMasking.CreateOrUpdateRule(resourceGroupName, server.Name, database.Name, rule2Name, ruleParams2);
TestUtilities.ValidateOperationResponse(createSecondRuleResponse, HttpStatusCode.OK);
DataMaskingRuleListResponse listAfterSecondCreateResponse = sqlClient.DataMasking.List(resourceGroupName, server.Name, database.Name);
TestUtilities.ValidateOperationResponse(listAfterSecondCreateResponse, HttpStatusCode.OK);
Assert.Equal(2, listAfterSecondCreateResponse.DataMaskingRules.Count);
updatedRule = listUpdateResponse.DataMaskingRules.FirstOrDefault(isRuleOnColumn(ruleParams));
VerifyDataMaskingRuleInformation(updatedRule.Properties, ruleParams.Properties);
var receivedSecondRule = listAfterSecondCreateResponse.DataMaskingRules.FirstOrDefault(isRuleOnColumn(ruleParams2));
VerifyDataMaskingRuleInformation(receivedSecondRule.Properties, ruleParams2.Properties);
AzureOperationResponse deleteResponse = sqlClient.DataMasking.Delete(resourceGroupName, server.Name, database.Name, rule1Name);
TestUtilities.ValidateOperationResponse(deleteResponse, HttpStatusCode.OK);
DataMaskingRuleListResponse listAfterDeleteResponse = sqlClient.DataMasking.List(resourceGroupName, server.Name, database.Name);
TestUtilities.ValidateOperationResponse(listAfterDeleteResponse, HttpStatusCode.OK);
Assert.Equal(listAfterDeleteResponse.DataMaskingRules.Count, 1);
var receivedAfterDelete = listAfterSecondCreateResponse.DataMaskingRules.FirstOrDefault(isRuleOnColumn(ruleParams2));
VerifyDataMaskingRuleInformation(receivedAfterDelete.Properties, ruleParams2.Properties);
}
/// <summary>
/// The non-boilerplated test code of the APIs for managing the lifecycle data masking rules.
/// It is meant to be called with a name of an already exisiting database (and therefore already existing server and resource group).
/// This test does not create these resources and does not remove them.
/// The flow is:
/// 1) Create policy (it's a prereq)
/// 2) Create rule1, validate its creation and its content by doing another GET call
/// 3) Update rule1, validate the update by doing another GET call
/// 4) Create rule2, validate its creation and its content
/// 5) Get the list of rules, see that there are two and each one of them has the right content
/// 6) Delete rule1, see that we get OK
/// 8) List the rules, see that we now have one rule there and it is rule 2
/// </summary>
/// <param name="sqlClient">The sqlClient</param>
/// <param name="resourceGroupName">The resource group name to use in this test</param>
/// <param name="server">The server to use in this test</param>
/// <param name="database">The database to use in this test</param>
private void TestDataMaskingRuleAPIs(SqlManagementClient sqlClient, string resourceGroupName, Server server, Database database)
{
DataMaskingPolicyCreateOrUpdateParameters policyParams = new DataMaskingPolicyCreateOrUpdateParameters();
policyParams.Properties = MakeDefaultDataMaskingPolicyProperties();
policyParams.Properties.MaskingLevel = "Relaxed";
sqlClient.DataMasking.CreateOrUpdatePolicy(resourceGroupName, server.Name, database.Name, policyParams);
int ruleCounter = 1;
DataMaskingRuleCreateOrUpdateParameters ruleParams = new DataMaskingRuleCreateOrUpdateParameters();
ruleParams.Properties = MakeRuleProperties(ruleCounter++);
string rule1Name = ruleParams.Properties.Id;
var createRuleResponse = sqlClient.DataMasking.CreateOrUpdateRule(resourceGroupName, server.Name, database.Name, rule1Name, ruleParams);
// Verify that the initial create request went well.
TestUtilities.ValidateOperationResponse(createRuleResponse, HttpStatusCode.OK);
var getAfterCreateResponse = sqlClient.DataMasking.GetRule(resourceGroupName, server.Name, database.Name, rule1Name);
TestUtilities.ValidateOperationResponse(getAfterCreateResponse, HttpStatusCode.OK);
VerifyDataMaskingRuleInformation(getAfterCreateResponse.DataMaskingRule.Properties, ruleParams.Properties);
// Modify the policy properties, send and receive, see it its still ok
ruleParams.Properties.TableName = "tbl2";
var updateResponse = sqlClient.DataMasking.CreateOrUpdateRule(resourceGroupName, server.Name, database.Name, rule1Name, ruleParams);
TestUtilities.ValidateOperationResponse(updateResponse, HttpStatusCode.OK);
var getAfterUpdateResponse = sqlClient.DataMasking.GetRule(resourceGroupName, server.Name, database.Name, rule1Name);
TestUtilities.ValidateOperationResponse(getAfterUpdateResponse, HttpStatusCode.OK);
VerifyDataMaskingRuleInformation(getAfterUpdateResponse.DataMaskingRule.Properties, ruleParams.Properties);
DataMaskingRuleCreateOrUpdateParameters ruleParams2 = new DataMaskingRuleCreateOrUpdateParameters();
ruleParams2.Properties = MakeRuleProperties(ruleCounter++);
string rule2Name = ruleParams2.Properties.Id;
createRuleResponse = sqlClient.DataMasking.CreateOrUpdateRule(resourceGroupName, server.Name, database.Name, rule2Name, ruleParams2);
TestUtilities.ValidateOperationResponse(updateResponse, HttpStatusCode.OK);
DataMaskingRuleListResponse listResponse = sqlClient.DataMasking.List(resourceGroupName, server.Name, database.Name);
TestUtilities.ValidateOperationResponse(listResponse, HttpStatusCode.OK);
Assert.Equal(2, listResponse.DataMaskingRules.Count);
VerifyDataMaskingRuleInformation(listResponse.DataMaskingRules.FirstOrDefault(r => r.Name == rule1Name).Properties, ruleParams.Properties);
VerifyDataMaskingRuleInformation(listResponse.DataMaskingRules.FirstOrDefault(r => r.Name == rule2Name).Properties, ruleParams2.Properties);
AzureOperationResponse deleteResponse = sqlClient.DataMasking.Delete(resourceGroupName, server.Name, database.Name, rule1Name);
TestUtilities.ValidateOperationResponse(deleteResponse, HttpStatusCode.OK);
DataMaskingRuleListResponse listAfterDeleteResponse = sqlClient.DataMasking.List(resourceGroupName, server.Name, database.Name);
TestUtilities.ValidateOperationResponse(listAfterDeleteResponse, HttpStatusCode.OK);
Assert.Equal(listAfterDeleteResponse.DataMaskingRules.Count, 1);
VerifyDataMaskingRuleInformation(listResponse.DataMaskingRules.FirstOrDefault(r => r.Name == rule2Name).Properties, ruleParams2.Properties);
}
/// <summary>
/// Create upgrade start parameters for testing
/// </summary>
/// <param name="version">The version to upgrade the server to</param>
/// <param name="scheduleUpgradeAfter">The earliest time to upgrade the server</param>
/// <param name="database">The database to map to new edition and SLO. Target SLO and edition are in the constants at the top of this file</param>
/// <returns>The server upgrade start parameters object</returns>
private ServerUpgradeStartParameters CreateUpgradeStartParameters(
string version = upgradedVersion,
DateTime? scheduleUpgradeAfter = null,
Database database = null)
{
var parameters = new ServerUpgradeStartParameters()
{
Properties = new ServerUpgradeProperties()
{
Version = version,
ScheduleUpgradeAfterUtcDateTime = scheduleUpgradeAfter
}
};
if (database != null)
{
parameters.Properties.DatabaseCollection = new List<RecommendedDatabaseProperties>()
{
new RecommendedDatabaseProperties()
{
Name = database.Name,
TargetServiceLevelObjective = targetServiceLevelObjective,
TargetEdition = targetEdition
}
};
}
return parameters;
}
/// <summary>
/// Implementation of the negative test for upgrade server.
/// </summary>
/// <param name="sqlClient">The SQL Management client</param>
/// <param name="resourceGroupName">The resource group containing the server to upgrade</param>
/// <param name="server">The server to upgrade</param>
/// <param name="database">The database under server that will be mapped to new edition and SLO</param>
private void UpgradeServerNegative(SqlManagementClient sqlClient, string resourceGroupName, Server server, Database database)
{
// Null or empty parameter for upgrade
Assert.Throws<ArgumentNullException>(() => sqlClient.ServerUpgrades.Start(resourceGroupName, server.Name, null));
Assert.Throws<ArgumentNullException>(() => sqlClient.ServerUpgrades.Start(resourceGroupName, server.Name, new ServerUpgradeStartParameters()));
// Invalid version
var invalidVersionParameters = CreateUpgradeStartParameters(version: "13.0");
Assert.Throws<CloudException>(() => sqlClient.ServerUpgrades.Start(resourceGroupName, server.Name, invalidVersionParameters));
// Invalid ScheduleAfter time
var invalidScheduleParameters = CreateUpgradeStartParameters(scheduleUpgradeAfter: DateTime.UtcNow);
Assert.Throws<CloudException>(() => sqlClient.ServerUpgrades.Start(resourceGroupName, server.Name, invalidScheduleParameters));
// Invalid edition
var invalidEditionParameters = CreateUpgradeStartParameters(database: database);
invalidEditionParameters.Properties.DatabaseCollection[0].TargetEdition = "InvalidEdition";
Assert.Throws<CloudException>(() => sqlClient.ServerUpgrades.Start(resourceGroupName, server.Name, invalidEditionParameters));
// Invalid edition and slo combination
var mismatchedSloAndEditionParameters = CreateUpgradeStartParameters(database: database);
mismatchedSloAndEditionParameters.Properties.DatabaseCollection[0].TargetEdition = "Premium";
mismatchedSloAndEditionParameters.Properties.DatabaseCollection[0].TargetServiceLevelObjective = "S0";
Assert.Throws<CloudException>(() => sqlClient.ServerUpgrades.Start(resourceGroupName, server.Name, mismatchedSloAndEditionParameters));
}
/// <summary>
/// Implementation of the positive test to upgrade server and poll for upgrade status until the upgrade is completed.
/// The test server has 1 database that will be mapped to a new edition and SLO after the upgrade
/// </summary>
/// <param name="sqlClient">The SQL Management client</param>
/// <param name="resourceGroupName">The resource group containing the server to upgrade</param>
/// <param name="server">The server to upgrade</param>
/// <param name="database">The database under server that will be mapped to new edition and SLO</param>
private void UpgradeServer(SqlManagementClient sqlClient, string resourceGroupName, Server server, Database database)
{
var parameters = CreateUpgradeStartParameters(database: database);
var startResponse = sqlClient.ServerUpgrades.Start(resourceGroupName, server.Name, parameters);
Assert.True(startResponse.StatusCode == HttpStatusCode.Accepted);
// Get server upgrade status
while (true)
{
var getUpgradeResponse = sqlClient.ServerUpgrades.Get(resourceGroupName, server.Name);
if (getUpgradeResponse.StatusCode == HttpStatusCode.OK)
{
Debug.WriteLine(getUpgradeResponse);
break;
}
// status must be Queued or InProgress
Assert.True(getUpgradeResponse.Status.Equals(upgradeStatusQueued, StringComparison.InvariantCultureIgnoreCase) ||
getUpgradeResponse.Status.Equals(upgradeStatusInProgress, StringComparison.InvariantCultureIgnoreCase));
System.Threading.Thread.Sleep(TimeSpan.FromSeconds(UpgradePollingTimeInSeconds));
}
// Make sure that server has new version
var serverGetResponse = sqlClient.Servers.Get(resourceGroupName, server.Name);
TestUtilities.ValidateOperationResponse(serverGetResponse);
Assert.Equal(serverGetResponse.Server.Properties.Version, upgradedVersion);
if (database != null)
{
// Make sure that database has new edition
var databaseGetResponse = sqlClient.Databases.Get(resourceGroupName, server.Name, database.Name);
TestUtilities.ValidateOperationResponse(databaseGetResponse);
Assert.Equal(databaseGetResponse.Database.Properties.Edition, targetEdition);
}
}
/// <summary>
/// Create upgrade start parameters for testing
/// </summary>
/// <param name="version">The version to upgrade the server to</param>
/// <param name="scheduleUpgradeAfter">The earliest time to upgrade the server</param>
/// <param name="recommendedDatabase">The database to map to new edition and SLO. Target SLO and edition are in the constants at the top of this file</param>
/// <param name="databaseInElasticPool">The database to be put in new elastic pool. Elastic pool properties are in the constants at the top of this file</param>
/// <returns>The server upgrade start parameters object</returns>
private ServerUpgradeStartParameters CreateUpgradeStartParameters(
string version = upgradedVersion,
DateTime? scheduleUpgradeAfter = null,
Database recommendedDatabase = null,
Database databaseInElasticPool = null)
{
var parameters = new ServerUpgradeStartParameters()
{
Properties = new ServerUpgradeProperties()
{
Version = version,
ScheduleUpgradeAfterUtcDateTime = scheduleUpgradeAfter
}
};
if (recommendedDatabase != null)
{
parameters.Properties.DatabaseCollection = new List<RecommendedDatabaseProperties>()
{
new RecommendedDatabaseProperties()
{
Name = recommendedDatabase.Name,
TargetServiceLevelObjective = targetServiceLevelObjective,
TargetEdition = targetEdition
}
};
}
if (databaseInElasticPool != null)
{
parameters.Properties.ElasticPoolCollection = new List<UpgradeRecommendedElasticPoolProperties>()
{
// Create an elastic pool with default values and contain only the provided database
new UpgradeRecommendedElasticPoolProperties()
{
Name = TestUtilities.GenerateName("csm-ep-"),
Edition = targetEdition,
Dtu = defaultElasticPoolDtu,
StorageMb = defaultElasticPoolStorageMb,
DatabaseDtuMax = defaultElasticPoolDatabaseDtuMax,
DatabaseDtuMin = defaultElasticPoolDatabaseDtuMin,
DatabaseCollection = new List<string>() { databaseInElasticPool.Name }
}
};
}
return parameters;
}
/// <summary>
/// Implementation of the positive test to upgrade server and poll for upgrade status until the upgrade is completed.
/// The test server has 1 database that will be put into a new elastic pool after the upgrade
/// </summary>
/// <param name="sqlClient">The SQL Management client</param>
/// <param name="resourceGroupName">The resource group containing the server to upgrade</param>
/// <param name="server">The server to upgrade</param>
/// <param name="databaseInElasticPool">The database under server that will be put into a new elastic pool</param>
private void UpgradeServerWithElasticPool(SqlManagementClient sqlClient, string resourceGroupName, Server server, Database databaseInElasticPool)
{
var parameters = CreateUpgradeStartParameters(databaseInElasticPool: databaseInElasticPool);
var startResponse = sqlClient.ServerUpgrades.Start(resourceGroupName, server.Name, parameters);
Assert.True(startResponse.StatusCode == HttpStatusCode.Accepted);
// Get server upgrade status
while (true)
{
var getUpgradeResponse = sqlClient.ServerUpgrades.Get(resourceGroupName, server.Name);
if (getUpgradeResponse.StatusCode == HttpStatusCode.OK)
{
Debug.WriteLine(getUpgradeResponse);
break;
}
// status must be Queued or InProgress
Assert.True(getUpgradeResponse.Status.Equals(upgradeStatusQueued, StringComparison.InvariantCultureIgnoreCase) ||
getUpgradeResponse.Status.Equals(upgradeStatusInProgress, StringComparison.InvariantCultureIgnoreCase));
System.Threading.Thread.Sleep(TimeSpan.FromSeconds(upgradePollingTimeInSeconds));
}
// Make sure that server has new version
var serverGetResponse = sqlClient.Servers.Get(resourceGroupName, server.Name);
TestUtilities.ValidateOperationResponse(serverGetResponse);
Assert.Equal(serverGetResponse.Server.Properties.Version, upgradedVersion);
if (databaseInElasticPool != null)
{
// Make sure that elastic pool has desired properties
var elasticPoolListGetResponse = sqlClient.ElasticPools.List(resourceGroupName, server.Name);
TestUtilities.ValidateOperationResponse(elasticPoolListGetResponse);
Assert.Equal(elasticPoolListGetResponse.ElasticPools.Count, 1);
var elasticPool = elasticPoolListGetResponse.ElasticPools.FirstOrDefault();
var expectedElasticPool = parameters.Properties.ElasticPoolCollection.FirstOrDefault();
Assert.Equal(elasticPool.Name, expectedElasticPool.Name);
Assert.Equal(elasticPool.Properties.Dtu, expectedElasticPool.Dtu);
Assert.Equal(elasticPool.Properties.DatabaseDtuMin, expectedElasticPool.DatabaseDtuMin);
Assert.Equal(elasticPool.Properties.DatabaseDtuMax, expectedElasticPool.DatabaseDtuMax);
Assert.Equal(elasticPool.Properties.Edition, expectedElasticPool.Edition);
Assert.Equal(elasticPool.Properties.StorageMB, expectedElasticPool.StorageMb);
}
}