protected override bool CheckExistence()
{
var exists = false;
// Check existence
if (Parameters.Properties.ResourceGroupExists)
{
using (var client = new SqlManagementClient(GetCredentials()))
{
var listResult = client.Servers.ListAsync(Parameters.Tenant.SiteName).Result;
var server = listResult.Servers.FirstOrDefault(s => s.Name.Equals(Parameters.GetSiteName(Position)));
Parameters.Properties.Components.Add(new AzureComponent()
{
Name = Parameters.GetSiteName(Position),
Service = Service,
Exists = server != null
});
if (server != null)
{
Status = ProvisioningStatus.Warning;
Message = string.Format("{0} name {1} is already in use and will be updated.", Service, Parameters.GetSiteName(Position));
exists = true;
}
}
}
return exists;
}
/// <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 TestServerSecurityAlertApis(SqlManagementClient sqlClient, string resourceGroupName, Server server)
{
var getDefaultDatabaseSecurityAlertPolicyResponse = sqlClient.SecurityAlertPolicy.GetServerSecurityAlertPolicy(resourceGroupName, server.Name);
var properties = getDefaultDatabaseSecurityAlertPolicyResponse.SecurityAlertPolicy.Properties;
// Verify that the initial Get request contains the default policy.
TestUtilities.ValidateOperationResponse(getDefaultDatabaseSecurityAlertPolicyResponse);
VerifySecurityAlertPolicyInformation(GetDefaultServerSecurityAlertProperties(), properties);
// Modify the policy properties, send and receive, see it its still ok
ChangeSecurityAlertPolicy(properties);
var updateParams = new ServerSecurityAlertPolicyCreateOrUpdateParameters { Properties = properties };
/*var updateResponse = */sqlClient.SecurityAlertPolicy.CreateOrUpdateServerSecurityAlertPolicy(resourceGroupName, server.Name, updateParams);
// Verify that the initial Get request contains the default policy.
// TestUtilities.ValidateOperationResponse(updateResponse);
var getUpdatedPolicyResponse = sqlClient.SecurityAlertPolicy.GetServerSecurityAlertPolicy(resourceGroupName, server.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 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>
/// 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);
}
private ServerDisasterRecoveryConfigurationCreateOrUpdateResponse CreateDrc(SqlManagementClient sqlClient, string resGroupName, Server server1, Server server2, string failoverAliasName)
{
ServerDisasterRecoveryConfigurationCreateOrUpdateParameters p = new ServerDisasterRecoveryConfigurationCreateOrUpdateParameters();
p.Properties = new ServerDisasterRecoveryConfigurationCreateOrUpdateProperties
{
AutoFailover = "Off",
FailoverPolicy = "Off",
PartnerServerId = string.Format(CultureInfo.InvariantCulture,
"/subscriptions/{0}/resourceGroups/{1}/providers/Microsoft.Sql/servers/{2}",
sqlClient.Credentials.SubscriptionId, resGroupName, server2.Name)
};
p.Location = server1.Location;
return sqlClient.ServerDisasterRecoveryConfigurations.CreateOrUpdate(resGroupName, server1.Name, failoverAliasName, p);
}
private void CreateDatabase()
{
// Deploy Database
using (var client = new SqlManagementClient(GetCredentials()))
{
// Create database
var createResult = client.Databases.CreateOrUpdateAsync(
Parameters.Tenant.SiteName,
Parameters.GetSiteName(Position),
Parameters.Tenant.DatabaseName,
new DatabaseCreateOrUpdateParameters()
{
Location = Parameters.Location(Position),
Properties =
new DatabaseCreateOrUpdateProperties()
}).Result;
}
}
protected override bool CheckExistence()
{
if (Parameters.Properties.ResourceGroupExists)
{
using (var client = new SqlManagementClient(GetCredentials()))
{
var serverList = client.Servers.ListAsync(Parameters.Tenant.SiteName).Result;
var server = serverList.Servers.FirstOrDefault(s => s.Name.Equals(Parameters.GetSiteName(Position)));
if (server != null)
{
var databaseList = client.Databases.ListAsync(Parameters.Tenant.SiteName, Parameters.GetSiteName(Position)).Result;
return databaseList.Databases.Any(database => database.Name.Equals(Parameters.Tenant.DatabaseName));
}
}
}
return false;
}
protected override bool CheckExistence()
{
if (Parameters.Properties.ResourceGroupExists)
{
using (var client = new SqlManagementClient(GetCredentials()))
{
var serverList = client.Servers.ListAsync(Parameters.Tenant.SiteName).Result;
var server = serverList.Servers.FirstOrDefault(s => s.Name.Equals(Parameters.GetSiteName(Position)));
//if (server != null)
//{
// var getResult = client.AuditingPolicy.GetServerPolicyAsync(Parameters.Tenant.SiteName, Parameters.GetSiteName(Position)).Result;
// return getResult.AuditingPolicy != null;
//}
}
}
return false;
}
protected override bool CreateOrUpdate()
{
var created = true;
try
{
// Skip if exists
if (!CheckExistence())
{
// Sleep for 30 seconds to give the Traffic Manager Some time
// known bug if auditing switched on too fast
Thread.Sleep(30000);
using (var client = new SqlManagementClient(GetCredentials()))
{
var createResult = client.AuditingPolicy.CreateOrUpdateServerPolicyAsync(
Parameters.Tenant.SiteName,
Parameters.GetSiteName(Position),
new ServerAuditingPolicyCreateOrUpdateParameters()
{
Properties = new ServerAuditingPolicyProperties()
{
AuditingState = "Enabled",
StorageAccountKey = Parameters.Tenant.StoragePrimaryKey,
StorageAccountName = Parameters.Tenant.SiteName,
StorageAccountResourceGroupName = Parameters.Tenant.SiteName,
StorageAccountSubscriptionId = Settings.AccountSubscriptionId,
EventTypesToAudit = "PlainSQL_Success,PlainSQL_Failure,ParameterizedSQL_Success,ParameterizedSQL_Failure,StoredProcedure_Success,StoredProcedure_Success"
}
}).Result;
}
}
}
catch (Exception ex)
{
created = false;
Message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
}
return created;
}
/// <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>
/// Implementation of the positive test to upgrade server, get upgrade status and cancel the upgrade successfully.
/// </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>
private void UpgradeAndCancelEmptyServer(SqlManagementClient sqlClient, string resourceGroupName, Server server)
{
var parameters = CreateUpgradeStartParameters();
var startResponse = sqlClient.ServerUpgrades.Start(resourceGroupName, server.Name, parameters);
Assert.True(startResponse.StatusCode == HttpStatusCode.Accepted);
// Polling for the upgrade status. When it's InProgress, we issue Cancel and verify that the status becomes Cancelling
// and then BadRequest when the upgrade is cancelled
bool cancelSent = false;
while (true)
{
ServerUpgradeGetResponse getUpgradeResponse;
try
{
// Get the upgrade status
getUpgradeResponse = sqlClient.ServerUpgrades.Get(resourceGroupName, server.Name);
}
catch (CloudException exception)
{
// Exception is expected when the cancel finishes
Debug.WriteLine(exception);
Assert.True(exception.Response.StatusCode == HttpStatusCode.BadRequest);
break;
}
if (cancelSent)
{
// status must be Cancelling
Assert.True(getUpgradeResponse.Status.Equals(upgradeStatusCancelling, StringComparison.InvariantCultureIgnoreCase));
}
else
{
Assert.True(getUpgradeResponse.Status.Equals(upgradeStatusQueued, StringComparison.InvariantCultureIgnoreCase) ||
getUpgradeResponse.Status.Equals(upgradeStatusInProgress, StringComparison.InvariantCultureIgnoreCase));
var cancelResponse = sqlClient.ServerUpgrades.Cancel(resourceGroupName, server.Name);
Assert.True(cancelResponse.StatusCode == HttpStatusCode.Accepted);
cancelSent = true;
}
System.Threading.Thread.Sleep(TimeSpan.FromSeconds(UpgradePollingTimeInSeconds));
}
var getResponse = sqlClient.Servers.Get(resourceGroupName, server.Name);
TestUtilities.ValidateOperationResponse(getResponse, HttpStatusCode.OK);
// Verify the server version is correct
Assert.Equal(getResponse.Server.Properties.Version, currentVersion);
}
/// <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>
/// 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);
}
}
/// <summary>
/// The non-boilerplated test code of the APIs for managing the lifecycle of a given server's blob auditing policy. It is meant to be called with a name of an already existing server (and therefore already existing
/// resource group). This test does not create these resources and does not remove them.
/// </summary>
private async void TestServerAuditingApis(SqlManagementClient sqlClient, string resourceGroupName, Server server)
{
var getDefaultServerPolicyResponse = sqlClient.BlobAuditing.GetServerPolicy(resourceGroupName, server.Name);
var properties = getDefaultServerPolicyResponse.AuditingPolicy.Properties;
// Verify that the initial Get request contains the default policy.
TestUtilities.ValidateOperationResponse(getDefaultServerPolicyResponse);
VerifyAuditingPolicyInformation(GetDefaultBlobAuditProperties(), properties);
// Modify the policy properties, send and receive, see it its still ok
ChangeBlobAuditPolicy(properties);
var updateParams = new BlobAuditingCreateOrUpdateParameters { Properties = properties };
var updateResponse = sqlClient.BlobAuditing.CreateOrUpdateServerPolicy(resourceGroupName, server.Name, updateParams);
var succeededInUpated = false;
// Verify that the initial Get request of contains the default policy.
TestUtilities.ValidateOperationResponse(updateResponse, HttpStatusCode.Accepted);
for (var iterationCount = 0; iterationCount < 10; iterationCount++) // at most 10 iterations, each means wait of 30 seconds
{
var blobAuditStatusResponse = sqlClient.BlobAuditing.GetOperationStatus(updateResponse.OperationStatusLink);
var blobAuditingOperationResult = blobAuditStatusResponse.OperationResult.Properties;
if (blobAuditingOperationResult.State == OperationStatus.Succeeded)
{
succeededInUpated = true;
break;
}
if (HttpMockServer.Mode != HttpRecorderMode.Playback)
{
await Task.Delay(30000);
}
}
Assert.True(succeededInUpated, "Failed in updating the server's policy");
var getUpdatedPolicyResponse = sqlClient.BlobAuditing.GetServerPolicy(resourceGroupName, server.Name);
var updatedProperties = getUpdatedPolicyResponse.AuditingPolicy.Properties;
// Verify that the Get request contains the updated policy.
TestUtilities.ValidateOperationResponse(getUpdatedPolicyResponse);
VerifyAuditingPolicyInformation(properties, updatedProperties);
}
/// <summary>
/// GETs and validates the server disaster recovery configuration
/// </summary>
/// <param name="sqlClient">The Sql client used to GET the server disaster recovery configuration</param>
/// <param name="resGroupName">The resource group of the server with the server disaster recovery configuration</param>
/// <param name="serverName">The server with the server disaster recovery configuration</param>
/// <param name="virtualEndpointName">The virtual endpoint name of the server disaster recovery configuration</param>
/// <param name="partnerServerName">The expected partner server in the server disaster recovery configuration</param>
/// <param name="primary">Whether the server disaster recovery configuration notes its server as having primary role</param>
private void GetAndValidateDrc(SqlManagementClient sqlClient, string resGroupName, string serverName, string virtualEndpointName, string partnerServerName, bool primary)
{
ServerDisasterRecoveryConfigurationGetResponse getResponse = sqlClient.ServerDisasterRecoveryConfigurations.Get(resGroupName, serverName, virtualEndpointName);
ServerDisasterRecoveryConfiguration drc = getResponse.ServerDisasterRecoveryConfiguration;
Assert.Equal(virtualEndpointName, drc.Name);
Assert.Equal(partnerServerName, drc.Properties.PartnerLogicalServerName);
Assert.Equal("Off", drc.Properties.AutoFailover);
Assert.Equal("Off", drc.Properties.FailoverPolicy);
Assert.Equal(primary ? "Primary" : "Secondary", drc.Properties.Role);
// Check the response from list call
ServerDisasterRecoveryConfigurationListResponse listResponse = sqlClient.ServerDisasterRecoveryConfigurations.List(resGroupName, serverName);
drc = listResponse.ServerDisasterRecoveryConfigurations.First();
Assert.Equal(virtualEndpointName, drc.Name);
Assert.Equal(partnerServerName, drc.Properties.PartnerLogicalServerName);
Assert.Equal("Off", drc.Properties.AutoFailover);
Assert.Equal("Off", drc.Properties.FailoverPolicy);
Assert.Equal(primary ? "Primary" : "Secondary", drc.Properties.Role);
}
/// <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>
/// Implementation of the positive test to upgrade server and poll for upgrade status until the upgrade is completed.
/// The test server is empty
/// </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>
private void UpgradeEmptyServer(SqlManagementClient sqlClient, string resourceGroupName, Server server)
{
UpgradeServer(sqlClient, resourceGroupName, server, null);
}
private void validateDrcNotExist(SqlManagementClient sqlClient, string resGroupName, string serverName, string failoverAliasName)
{
Assert.Throws<Hyak.Common.CloudException>(() => sqlClient.ServerDisasterRecoveryConfigurations.Get(resGroupName,
serverName, failoverAliasName));
}
/// <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>
/// 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>
/// 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>
/// A helper method that creates only a database within the given resource group and server. Once it is created this method calls the
/// given test with the sql client and the names of the resource group, server and database.
/// </summary>
private static void RunDbTest(SqlManagementClient sqlClient, string resGroupName, Server server, Action<SqlManagementClient, string, Server, Database> test)
{
// Variables for database create
string databaseName = TestUtilities.GenerateName("csm-auditing-db");
string databaseCollation = "Japanese_Bushu_Kakusu_100_CS_AS_KS_WS";
string databaseEdition = "Basic";
long databaseMaxSize = 1L * 1024L * 1024L * 1024L; // 1 GB
Guid dbSloBasic = new Guid("dd6d99bb-f193-4ec1-86f2-43d3bccbc49c"); // Basic
//////////////////////////////////////////////////////////////////////
// Create database for test.
var database = sqlClient.Databases.CreateOrUpdate(resGroupName, server.Name, databaseName, new DatabaseCreateOrUpdateParameters()
{
Location = server.Location,
Properties = new DatabaseCreateOrUpdateProperties()
{
Collation = databaseCollation,
Edition = databaseEdition,
MaxSizeBytes = databaseMaxSize,
RequestedServiceObjectiveId = dbSloBasic,
},
}).Database;
test(sqlClient, resGroupName, server, database);
}
private void CreateFirewallRule()
{
using (var client = new SqlManagementClient(GetCredentials()))
{
// Sleep for 30 seconds to give the Firewall setting some time
// known bug if switched on too fast
Thread.Sleep(30000);
var firewallResult = client.FirewallRules.CreateOrUpdateAsync(Parameters.Tenant.SiteName, Parameters.GetSiteName(Position), "OpenAll",
new FirewallRuleCreateOrUpdateParameters()
{
Properties = new FirewallRuleCreateOrUpdateProperties()
{
StartIpAddress = "0.0.0.0",
EndIpAddress = "255.255.255.255",
}
}).Result;
}
}
private void CreateServer()
{
using (var client = new SqlManagementClient(GetCredentials()))
{
var createResult = client.Servers.CreateOrUpdateAsync(Parameters.Tenant.SiteName, Parameters.GetSiteName(Position),
new ServerCreateOrUpdateParameters()
{
Location = Parameters.Location(Position),
Properties = new ServerCreateOrUpdateProperties()
{
AdministratorLogin = Parameters.Tenant.UserName,
AdministratorLoginPassword = Parameters.Tenant.Password,
Version = Parameters.Tenant.SqlVersion
}
}).Result;
}
}
