def delete_databases(sql_list):
credentials, subscription_id = get_credentials()
sql_client = SqlManagementClient(credentials, subscription_id)
for item in sql_list:
rg = item[0]
dbName = item[1]
sql_client.servers.delete(rg, dbName)
def create_azure_fw_rule(status_dict):
RESOURCE_GROUP = os.environ['RESOURCE_GROUP']
subscription_id = os.environ.get('AZURE_SUBSCRIPTION_ID',
'11111111-1111-1111-1111-111111111111')
credentials = ServicePrincipalCredentials(
client_id=os.environ['AZURE_CLIENT_ID'],
secret=os.environ['AZURE_CLIENT_SECRET'],
tenant=os.environ['AZURE_TENANT_ID'])
resource_client = ResourceManagementClient(credentials, subscription_id)
sql_client = SqlManagementClient(credentials, subscription_id)
resource_client.providers.register('Microsoft.Sql')
for index in range(len(status_dict['ip_list'])):
for entry in status_dict['ip_list'][index]:
if type(entry) == dict:
SQL_SERVER = status_dict['db_instance_identifier'][index]
if '/' in entry['start']:
n = ipaddress.IPv4Network(entry['start'])
entry['start'], entry['end'] = n[0], n[-1]
rule_name = 'firewall_rule_for_ip_list_' + str(entry['start'])
firewall_rule = sql_client.firewall_rules.create_or_update(
RESOURCE_GROUP, SQL_SERVER, rule_name, entry['start'],
entry['end'])
print('Adding the rule', firewall_rule)
else:
SQL_SERVER = status_dict['db_instance_identifier'][index]
rule_name = 'firewall_rule_for_workload_' + str(entry)
firewall_rule = sql_client.firewall_rules.create_or_update(
RESOURCE_GROUP, SQL_SERVER, rule_name, entry, entry)
print('Adding the rule', firewall_rule)
return firewall_rule
def get_client(credentials: Credentials,
subscription_id: str) -> SqlManagementClient:
"""
Getting the Azure SQL client
"""
client = SqlManagementClient(credentials, subscription_id)
return client
def set_connect(self, secret_data):
subscription_id = secret_data['subscription_id']
os.environ["AZURE_SUBSCRIPTION_ID"] = subscription_id
os.environ["AZURE_TENANT_ID"] = secret_data['tenant_id']
os.environ["AZURE_CLIENT_ID"] = secret_data['client_id']
os.environ["AZURE_CLIENT_SECRET"] = secret_data['client_secret']
credential = DefaultAzureCredential()
self.compute_client = ComputeManagementClient(
credential=credential, subscription_id=subscription_id)
self.vm_compute_client = ComputeManagementClient(
credential=credential,
subscription_id=subscription_id,
expand='instanceView')
self.resource_client = ResourceManagementClient(
credential=credential, subscription_id=subscription_id)
self.network_client = NetworkManagementClient(
credential=credential, subscription_id=subscription_id)
self.subscription_client: SubscriptionClient = SubscriptionClient(
credential=credential)
self.sql_client = SqlManagementClient(credential=credential,
subscription_id=subscription_id)
self.monitor_client = MonitorManagementClient(
credential=credential, subscription_id=subscription_id)
def create_firewall_rule_files(subscription_id, sql_server,
resource_group_name):
rule_dict = {}
subscription_id = os.environ.get('AZURE_SUBSCRIPTION_ID', subscription_id)
credentials = ServicePrincipalCredentials(client_id=client_id,
secret=client_secret,
tenant=tenant)
sql_client = SqlManagementClient(credentials, subscription_id)
subscription_name = subscriptions_dict[subscription_id]
print('{}/{}: {}/{}'.format(subscription_name, subscription_id,
resource_group_name, sql_server))
firewall_rules = sql_client.firewall_rules.list_by_server(
resource_group_name, sql_server)
for firewall_rule in firewall_rules:
start_ip = str(firewall_rule.start_ip_address)
end_ip = str(firewall_rule.end_ip_address)
ip_range = '{}:{}'.format(start_ip, end_ip)
rule_name = str(firewall_rule.name)
if start_ip == end_ip:
rule_dict[rule_name] = start_ip
else:
rule_dict[rule_name] = ip_range
print('rule_dict: {}'.format(rule_dict))
write_rule_dict_to_file(subscription_name, resource_group_name, sql_server,
rule_dict)
def azure_connect_service(service, credentials, region_name=None):
try:
if service == 'storageaccounts':
return StorageManagementClient(credentials.credentials, credentials.subscription_id)
elif service == 'monitor':
return MonitorManagementClient(credentials.credentials, credentials.subscription_id)
elif service == 'sqldatabase':
return SqlManagementClient(credentials.credentials, credentials.subscription_id)
elif service == 'keyvault':
return KeyVaultManagementClient(credentials.credentials, credentials.subscription_id)
elif service == 'appgateway':
return NetworkManagementClient(credentials.credentials, credentials.subscription_id)
elif service == 'network':
return NetworkManagementClient(credentials.credentials, credentials.subscription_id)
elif service == 'rediscache':
return RedisManagementClient(credentials.credentials, credentials.subscription_id)
elif service == 'securitycenter':
return SecurityCenter(credentials.credentials, credentials.subscription_id, '')
elif service == 'appservice':
return WebSiteManagementClient(credentials.credentials, credentials.subscription_id)
elif service == 'loadbalancer':
return NetworkManagementClient(credentials.credentials, credentials.subscription_id)
else:
printException('Service %s not supported' % service)
return None
except Exception as e:
printException(e)
return None
def run_action(credentials, rule, entity, params):
logging.info(f'{__file__} - ${run_action.__name__} started')
entity_id = entity.get('id')
split_id = entity_id.split('/')
subscription_id = split_id[2]
group_name = split_id[4]
server_name = split_id[8]
print(group_name)
database_name = split_id[10]
tde_name = 'current'
logging.info(
f'{__file__} - subscription_id : {subscription_id} - group_name : {group_name} - server_name : {server_name} - database_name : {database_name}'
)
if not subscription_id or not credentials:
return raise_credentials_error()
try:
sql_client = SqlManagementClient(credentials, subscription_id)
sql_client.transparent_data_encryptions.create_or_update(
group_name,
server_name,
database_name,
tde_name,
parameters=TransparentDataEncryption(
status=TransparentDataEncryptionStatus.enabled))
msg = f'Transparent data encryption enabled successfully on database : {database_name}'
logging.info(f'{__file__} - {msg}')
return f'{msg}'
except HttpResponseError as e:
msg = f'Unexpected error : {e.message}'
logging.info(f'{__file__} - {msg}')
return msg
def discover_resources(**kwargs):
discovered_azure_sql = []
for handler in AzureARMHandler.objects.all():
set_progress('Connecting to Azure sql \
DB for handler: {}'.format(handler))
credentials = ServicePrincipalCredentials(client_id=handler.client_id,
secret=handler.secret,
tenant=handler.tenant_id)
azure_client = SqlManagementClient(credentials, handler.serviceaccount)
azure_resources_client = resources.ResourceManagementClient(
credentials, handler.serviceaccount)
for resource_group in azure_resources_client.resource_groups.list():
try:
for server in azure_client.servers.list_by_resource_group(
resource_group.name)._get_next().json()['value']:
discovered_azure_sql.append({
'name':
server['name'],
'azure_server_name':
server['name'],
'resource_group_name':
resource_group.name,
'azure_rh_id':
handler.id
})
except CloudError:
continue
return discovered_azure_sql
def run_action(credentials, rule, entity, params):
logging.info(f'{__file__} - ${run_action.__name__} started')
azure_ad_admin_name, azure_ad_user_sid, tenant = params
group_name = entity.get('resourceGroup', {}).get('name')
subscription_id = entity.get('accountNumber')
server_name = entity.get('name')
admin_name = 'activeDirectory'
admin_params = 'administrator_type'
logging.info(
f'{__file__} - subscription_id : {subscription_id} - group_name : {group_name} - server_name : {server_name} - azure_ad_admin : {azure_ad_admin_name} - azure_user_sid : {azure_ad_user_sid} - tenant : {tenant}'
)
if not subscription_id or not credentials:
return raise_credentials_error()
try:
sql_client = SqlManagementClient(credentials, subscription_id)
sql_client.server_azure_ad_administrators.begin_create_or_update(
group_name, server_name, admin_name,
ServerAzureADAdministrator(
administrator_type=AdministratorType("ActiveDirectory"),
login=azure_ad_admin_name,
sid=azure_ad_user_sid,
tenant_id=tenant))
msg = f'Azure AD Administrator authentication enabled successfully on database server: {server_name}'
logging.info(f'{__file__} - {msg}')
return f'{msg}'
except HttpResponseError as e:
msg = f'Unexpected error : {e.message}'
logging.info(f'{__file__} - {msg}')
return msg
def run(self,list_subscription_name,list_dbserver_type,subscription_id,client_id,client_tenant):
list_result = list()
for subscription_name in list_subscription_name:
print subscription_name
for dbserver_type in list_dbserver_type:
print dbserver_type
subscriptionid = subscription_id[subscription_name]
client = Client(base_url='http://localhost')
client_secret = client.keys.get_by_name(name='azure_stackstorm_secret', decrypt=True)
credentials = ServicePrincipalCredentials(client_id, client_secret.value, tenant=client_tenant, china=True)
if dbserver_type == "mysql":
dbserverclient = MySQLManagementClient(credentials, subscriptionid, base_url="https://management.chinacloudapi.cn")
if dbserver_type == "postgresql":
dbserverclient = PostgreSQLManagementClient(credentials, subscriptionid, base_url="https://management.chinacloudapi.cn")
if dbserver_type == "sqlserver":
dbserverclient = SqlManagementClient(credentials, subscriptionid, base_url="https://management.chinacloudapi.cn")
list_dict_resgroup_dbserver = self._list_all_dbserverserver(dbserverclient)
num = len(list_dict_resgroup_dbserver)
for n in range(0, num):
dict_resgroup_rules = dict()
list_rule =self._list_firewall_rule(dbserverclient,list_dict_resgroup_dbserver[n]["resource_group"],list_dict_resgroup_dbserver[n]["dbserver_name"])
dict_resgroup_rules["resource_group"] = list_dict_resgroup_dbserver[n]["resource_group"]
dict_resgroup_rules["dbserver_name"] = list_dict_resgroup_dbserver[n]["dbserver_name"]
dict_resgroup_rules["rule_name"] = list_rule
now_time = datetime.datetime.now().strftime("%y-%m-%d")
rule_num = len(dict_resgroup_rules["rule_name"])
for i in range(0, rule_num):
if dict_resgroup_rules["rule_name"][i].split('_')[0] == now_time:
result = self._delete_rule(dbserverclient,dict_resgroup_rules["resource_group"],dict_resgroup_rules["dbserver_name"],dict_resgroup_rules["rule_name"][i])
list_result.append(result)
return (True,list_result)
def createSqlServer():
if request.method == 'GET':
try:
logged = session['loggedIn']
except:
logged = 'F'
if logged == 'T':
form = CreateSqlServerForm()
user = session['user']
pasw = session['pasw']
credentials = cred.UserPassCredentials(user, pasw, verify=True)
resource_client = ResourceManagementClient(credentials, session['subscription_id'])
resGroups = []
for resC in resource_client.resource_groups.list():
resGroups.append((resC.as_dict()['name'], resC.as_dict()['name']))
form.rg_name.choices = resGroups
form.subscriptionID.data = session['subscription_id']
return render_template('sqlserver.html', title='Create Sql Server', form=form)
else:
flash("You must login before creating resources!!")
return redirect('/index')
else:
try:
form = CreateSqlServerForm()
credentials = cred.UserPassCredentials(session['user'], session['pasw'], verify=True)
subsID = form.subscriptionID.data
RGName = form.rg_name.data
location = form.region.data
sql_svname = form.sql_svname.data
sql_dbname = form.sql_dbname.data
dbuser = form.admin_user.data
dbpass = form.admin_pass.data
sql_client = SqlManagementClient(credentials=credentials, subscription_id=subsID)
sql_client.servers.create_or_update(
RGName,
sql_svname,
{
'location': location,
'version': '12.0', # Required for create
'administrator_login': dbuser, # Required for create
'administrator_login_password': dbpass # Required for create
}
)
async_db_create = sql_client.databases.create_or_update(
RGName,
sql_svname,
sql_dbname,
{
'location': location
}
)
async_db_create.result()
flash("Sql Server created successfully")
return redirect('/index')
except Exception as ex:
errorMessage = repr(ex)
flash("Cannot create sql server with those settings. " + errorMessage)
return redirect('/index')
def run_action(credentials, rule, entity, params):
logging.info(f'{__file__} - ${run_action.__name__} started')
subscription_id = entity['accountNumber']
server_group_name = entity['resourceGroup']['name']
sql_server_name = entity['name']
sql_server_region = entity['region']
logging.info(
f'{__file__} - subscription_id : {subscription_id} - server_group_name : {server_group_name} - sql_server : {sql_server_name}')
if not subscription_id or not credentials:
return raise_credentials_error()
try:
network_client = NetworkManagementClient(credentials,subscription_id)
sql_client = SqlManagementClient(credentials, subscription_id)
vnets = network_client.virtual_networks.list_all()
acls = []
endpoint_params = [ServiceEndpointPropertiesFormat(service='Microsoft.Sql', locations=["*"])]
subnet_path = ""
for v in vnets:
vnet_name = v.name
vnet_nsg_split = v.id.split('/')
vnet_nsg = vnet_nsg_split[4]
subnets = v.subnets
vnet_region = v.location
if sql_server_region == vnet_region:
logging.info(f'Regions match - applying ACLs to {vnet_name}')
for s in subnets:
subnet_path = s.id
subnet_name = s.name
subnet_address_prefix = s.address_prefix
service_endpoint_list = s.service_endpoints
firewall_rule_name = vnet_name + "-" + subnet_name + " auto-generated rule"
logging.info(f'Subnet path : {subnet_path} Subnet Name : {subnet_name} Subnet CIDR : {subnet_address_prefix} Endpoint list : {service_endpoint_list}')
# Create storage endpoint if doesn't exist
if not service_endpoint_list:
network_client.subnets.begin_create_or_update(resource_group_name=vnet_nsg, virtual_network_name=vnet_name, subnet_name=subnet_name,
subnet_parameters=Subnet(address_prefix=subnet_address_prefix, service_endpoints=endpoint_params))
else:
logging.info(f'Service Endpoint for subnet {subnet_name} already exists, not creating')
logging.info(*service_endpoint_list)
acls.append(VirtualNetworkRule(virtual_network_subnet_id=subnet_path))
sql_client.virtual_network_rules.begin_create_or_update(server_group_name, sql_server_name, firewall_rule_name, parameters=VirtualNetworkRule(
virtual_network_subnet_id=subnet_path, ignore_missing_vnet_service_endpoint=False))
logging.info(f'Azure SQL firewall rule {firewall_rule_name} set successfully on : {sql_server_name}')
else:
logging.info(f'Regions do not match - skipping {vnet_name}')
except (HttpResponseError, ResourceExistsError) as e:
logging.info(f'An error occured : {e}')
def list_databases():
credentials, subscription_id = get_credentials()
sql_client = SqlManagementClient(credentials, subscription_id)
sql_arry = []
for item in sql_client.servers.list():
db_id = item.id
regex_string = 'resourceGroups\/(.*)\/providers'
resource_group = re.search(regex_string, db_id).group(1)
sql_arry.append([resource_group,item.name])
return(sql_arry)
def __init__(self, sql_credentials: SQLCredentials, identity: Identity,
**options):
self.sql_credentials = sql_credentials
self.default_RG = options.get(
'resource_group_name') or "default_rg_python"
self.region = options.get('region') or "westus"
self.sku = options.get('sku') or Sku(name='Free')
self.db_object = None
self.resource_client = identity.resource_client
self.sql_client = SqlManagementClient(identity.credentials,
identity.subscription_id)
def run(self, args):
"""Run the remediation job.
:param args: List of arguments provided to the job.
:type args: list.
:returns: int
"""
params = self.parse(args[1])
client_id = os.environ.get("AZURE_CLIENT_ID")
client_secret = os.environ.get("AZURE_CLIENT_SECRET")
tenant_id = os.environ.get("AZURE_TENANT_ID")
# credential for Storage Account and Key Vault management and SQL client
credential = ClientSecretCredential(
client_id=client_id, client_secret=client_secret, tenant_id=tenant_id,
)
# credential for AzureGraphRbacManagementClient
credentials_graph = ServicePrincipalCredentials(
client_id=client_id,
secret=client_secret,
tenant=tenant_id,
resource="https://graph.windows.net",
)
client_storage = StorageManagementClient(credential, params["subscription_id"])
keyvault_client = KeyVaultManagementClient(
credential, params["subscription_id"]
)
graph_client = GraphRbacManagementClient(
credentials_graph, tenant_id, base_url=None
)
monitor_client = MonitorClient(credential, params["subscription_id"])
client = SqlManagementClient(
credential, params["subscription_id"], base_url=None
)
return self.remediate(
client_id,
tenant_id,
credential,
client,
client_storage,
keyvault_client,
graph_client,
monitor_client,
params["resource_group_name"],
params["sql_server_name"],
params["region"],
params["subscription_id"],
)
def get_clients_from_cli(subscription_id):
credentials, subscription_id, tenant_id = get_azure_cli_credentials(
resource=None, with_tenant=True, subscription_id=subscription_id)
print("creating subscription client")
subscription_client = SubscriptionClient(credentials)
print("creating compute client")
compute_client = ComputeManagementClient(credentials, subscription_id)
print("creating sql client")
sql_client = SqlManagementClient(credentials, subscription_client)
print("creating resource manager client")
rm_client = ResourceManagementClient(credentials, subscription_id)
return subscription_client, compute_client, rm_client, sql_client
def _create_sql_management_client(self) -> None:
"""Creates a `SqlManagementClient` object.
Overview:
----
Initalizes the `SqlManagementClient` object used
to manage different SQL Server Objects.
"""
# Initialize it.
sql_management_client = SqlManagementClient(
credentials=self.credentials, subscription_id=self.subscription_id)
self._sql_management_client = sql_management_client
self.authenticated = True
def main():
SUBSCRIPTION_ID = os.environ.get("SUBSCRIPTION_ID", None)
PASSWORD = os.environ.get("PASSWORD", None)
GROUP_NAME = "testgroupx"
SERVER_DNS_ALIAS = "serverdnsaliasxxyyzz"
SERVER = "serverxxy"
# Create client
# # For other authentication approaches, please see: https://pypi.org/project/azure-identity/
resource_client = ResourceManagementClient(
credential=DefaultAzureCredential(), subscription_id=SUBSCRIPTION_ID)
sql_client = SqlManagementClient(credential=DefaultAzureCredential(),
subscription_id=SUBSCRIPTION_ID)
# Create resource group
resource_client.resource_groups.create_or_update(GROUP_NAME,
{"location": "eastus"})
# - init depended resources -
# Create Server
server = sql_client.servers.begin_create_or_update(
GROUP_NAME, SERVER, {
"location": "eastus",
"administrator_login": "******",
"administrator_login_password": PASSWORD
}).result()
print("Create server:\n{}".format(server))
# - end -
# Create server dns alias
server_dns_alias = sql_client.server_dns_aliases.begin_create_or_update(
GROUP_NAME, SERVER, SERVER_DNS_ALIAS).result()
print("Create server dns alias:\n{}".format(server_dns_alias))
# Get server dns alias
server_dns_alias = sql_client.server_dns_aliases.get(
GROUP_NAME, SERVER, SERVER_DNS_ALIAS)
print("Get server dns alias:\n{}".format(server_dns_alias))
# Delete server dns alias
server_dns_alias = sql_client.server_dns_aliases.begin_delete(
GROUP_NAME, SERVER, SERVER_DNS_ALIAS).result()
print("Delete server dns alias.\n")
# Delete Group
resource_client.resource_groups.begin_delete(GROUP_NAME).result()
def get_clients_from_service_principals(tenant_id=None,
generate_credentials_ini=False,
generate_auth_file=False,
overwrite_ini=False):
"""
Create a client for each of the subscriptions in azureProfile.json for a tenant
"""
credentials_ini = ""
with open(AZURE_PROFILE_PATH, 'r') as f:
azure_profiles = yaml.load(f, Loader=yaml.Loader)['subscriptions']
for profile in azure_profiles:
if tenant_id and not (tenant_id == profile['tenantId']):
continue
subscription_id = profile['id']
service_principle_name = profile['name'] + '-' + subscription_id
result = call(
"az account set --subscription {}".format(subscription_id))
credentials = jsonify(call("az ad sp create-for-rbac --sdk-auth"))
credentials_ini += credentials_block(subscription_id, credentials,
service_principle_name)
if generate_auth_file:
azure_auth_location = os.path.join(
AZURE_CONFIG_DIR, service_principle_name + '.json')
with open(azure_auth_location, 'w') as f:
f.write(credentials)
sp_credentials = ServicePrincipalCredentials(
client_id=credentials['clientId'],
secret=credentials['clientSecret'],
tenant=credentials['tenantId'])
if generate_credentials_ini:
if overwrite_ini:
mode = 'w'
else:
mode = 'w+'
with open(AZURE_CREDENTIALS_PATH, mode) as f:
f.write(credentials_ini)
subscription_client = SubscriptionClient(credentials, subscription_id)
compute_client = ComputeManagementClient(credentials, subscription_id)
sql_client = SqlManagementClient(credentials, subscription_id)
yield subscription_client, compute_client, sql_client
def get_sql_management_client(cli_ctx):
from azure.cli.core.commands.client_factory import get_mgmt_service_client
from azure.mgmt.sql import SqlManagementClient
from msrest.authentication import Authentication
from os import getenv
# Allow overriding SQL resource manager URI using environment variable
# for testing purposes. Subscription id is also determined by environment
# variable.
sql_rm_override = getenv('_AZURE_CLI_SQL_RM_URI')
if sql_rm_override:
return SqlManagementClient(
subscription_id=getenv('_AZURE_CLI_SQL_SUB_ID'),
base_url=sql_rm_override,
credentials=Authentication()) # No authentication
# Normal production scenario.
return get_mgmt_service_client(cli_ctx, SqlManagementClient)
def getCredential(self):
self.credentials = ServicePrincipalCredentials(
client_id=self.client_id,
secret=self.secret,
tenant=self.tenant_id)
self.compute_client = ComputeManagementClient(self.credentials,
self.subscription_id)
self.network_client = NetworkManagementClient(self.credentials,
self.subscription_id)
self.storage_client = StorageManagementClient(self.credentials,
self.subscription_id)
self.container_regisry_client = ContainerRegistryManagementClient(
self.credentials, self.subscription_id)
self.sql_client = SqlManagementClient(self.credentials,
self.subscription_id)
self.dns_client = DnsManagementClient(self.credentials,
self.subscription_id)
def run_action(credentials, rule, entity, params):
firewall_rule_name, firewall_rule_vnet_rg_name, firewall_rule_vnet_name, firewall_rule_subnet_name = params
if not firewall_rule_name:
msg = 'Error! Firewall rule name is missing.'
logging.info(f'{__file__} - {msg}')
elif not firewall_rule_vnet_rg_name:
msg = 'Error! Firewall rule VNet resource group is missing.'
logging.info(f'{__file__} - {msg}')
elif not firewall_rule_vnet_name:
msg = 'Error! Firewall rule VNet name is missing.'
logging.info(f'{__file__} - {msg}')
elif not firewall_rule_subnet_name:
msg = 'Error! Firewall rule Subnet name is missing.'
logging.info(f'{__file__} - {msg}')
logging.info(f'{__file__} - ${run_action.__name__} started')
group_name = entity.get('resourceGroup', {}).get('name')
subscription_id = entity.get('accountNumber')
server_name = entity.get('name')
server_location = entity.get('region')
server_group_name = entity.get('resourceGroup', {}).get('name')
logging.info(
f'{__file__} - subscription_id : {subscription_id} - group_name : {group_name} - server_name : {server_name}')
if not subscription_id or not credentials:
return raise_credentials_error()
subnet_path = '/subscriptions/' + subscription_id + '/resourceGroups/' + firewall_rule_vnet_rg_name + \
'/providers/Microsoft.Network/virtualNetworks/' + \
firewall_rule_vnet_name + '/subnets/' + firewall_rule_subnet_name
try:
sql_client = SqlManagementClient(credentials, subscription_id)
sql_client.virtual_network_rules.begin_create_or_update(server_group_name, server_name, firewall_rule_name, parameters=VirtualNetworkRule(
virtual_network_subnet_id=subnet_path, ignore_missing_vnet_service_endpoint=False))
msg = f'Azure SQL firewall rule {firewall_rule_name} successfully on : {server_name}'
logging.info(f'{__file__} - {msg}')
return f'{msg}'
except HttpResponseError as e:
msg = f'Unexpected error : {e.message}'
logging.info(f'{__file__} - {msg}')
return msg
def build_sql_servers_dict(subscription_id, subscription_name):
sub_sql_servers_dict[subscription_id] = {}
subscription_id = os.environ.get('AZURE_SUBSCRIPTION_ID', subscription_id)
credentials = ServicePrincipalCredentials(client_id=client_id,
secret=client_secret,
tenant=tenant)
sql_client = SqlManagementClient(credentials, subscription_id)
sql_servers = sql_client.servers.list()
for server in sql_servers:
resourceGroupName = re.search(
'\/subscriptions\/[a-zA-Z0-9\-]+\/[a-zA-Z]+\/([a-zA-Z0-9\-\_]+)\/',
server.id, re.IGNORECASE)
resourceGroupName = resourceGroupName.group(1)
sub_sql_servers_dict[subscription_id][server.name] = resourceGroupName
def run(self, args):
"""Run the remediation job.
:param args: List of arguments provided to the job.
:type args: list.
:returns: int
"""
params = self.parse(args[1])
credentials = ServicePrincipalCredentials(
client_id=os.environ.get("AZURE_CLIENT_ID"),
secret=os.environ.get("AZURE_CLIENT_SECRET"),
tenant=os.environ.get("AZURE_TENANT_ID"),
)
client = SqlManagementClient(
credentials, params["subscription_id"], base_url=None
)
return self.remediate(
client, params["resource_group_name"], params["sql_server_name"],
)
def run_action(credentials, rule, entity, params):
min_tls_version = params
global min_tls
if 'tls_12' in min_tls_version:
min_tls = '1.2'
elif 'tls_11' in min_tls_version:
min_tls = '1.1'
elif 'tls_10' in min_tls_version:
min_tls = '1.0'
else:
msg = 'TLS version not defined correctly - should be tls_10, tls_11 or tls_12'
logging.info(f'{__file__} - {msg}')
return msg
logging.info(f'{__file__} - ${run_action.__name__} started')
group_name = entity.get('resourceGroup', {}).get('name')
subscription_id = entity.get('accountNumber')
server_name = entity.get('name')
server_location = entity.get('region')
logging.info(
f'{__file__} - subscription_id : {subscription_id} - group_name : {group_name} - server_name : {server_name}'
)
if not subscription_id or not credentials:
return raise_credentials_error()
try:
sql_client = SqlManagementClient(credentials, subscription_id)
sql_client.servers.begin_create_or_update(
group_name, server_name,
Server(location=server_location,
public_network_access='Disabled',
minimal_tls_version=min_tls))
msg = f'Azure SQL public network access disabled successfully on : {server_name}, TLS version set to {min_tls}'
logging.info(f'{__file__} - {msg}')
return f'{msg}'
except HttpResponseError as e:
msg = f'Unexpected error : {e.message}'
logging.info(f'{__file__} - {msg}')
return msg
def run(job, **kwargs):
resource = kwargs.pop('resources').first()
server_name = resource.attributes.get(
field__name='azure_server_name').value
resource_group = resource.attributes.get(
field__name='resource_group_name').value
rh_id = resource.attributes.get(field__name='azure_rh_id').value
rh = AzureARMHandler.objects.get(id=rh_id)
set_progress("Connecting To Azure...")
credentials = ServicePrincipalCredentials(client_id=rh.client_id,
secret=rh.secret,
tenant=rh.tenant_id)
client = SqlManagementClient(credentials, rh.serviceaccount)
set_progress("Connection to Azure established")
set_progress("Deleting server %s" % (server_name))
client.servers.delete(resource_group, server_name).wait()
return "", "", ""
def run_action(credentials, rule, entity, params):
firewall_rule_name, firewall_rule_start_ip, firewall_rule_end_ip = params
if not firewall_rule_name:
msg = 'Error! Firewall rule name is missing.'
logging.info(f'{__file__} - {msg}')
elif not firewall_rule_start_ip:
msg = 'Error! Firewall rule starting IP is missing.'
logging.info(f'{__file__} - {msg}')
elif not firewall_rule_end_ip:
msg = 'Error! Firewall rule ending IP is missing.'
logging.info(f'{__file__} - {msg}')
logging.info(f'{__file__} - ${run_action.__name__} started')
group_name = entity.get('resourceGroup', {}).get('name')
subscription_id = entity.get('accountNumber')
server_name = entity.get('name')
server_location = entity.get('region')
logging.info(
f'{__file__} - subscription_id : {subscription_id} - group_name : {group_name} - server_name : {server_name}'
)
if not subscription_id or not credentials:
return raise_credentials_error()
try:
sql_client = SqlManagementClient(credentials, subscription_id)
sql_client.firewall_rules.create_or_update(
group_name,
server_name,
firewall_rule_name,
parameters=FirewallRule(start_ip_address=firewall_rule_start_ip,
end_ip_address=firewall_rule_end_ip))
msg = f'Azure SQL firewall rule {firewall_rule_name} successfully on : {server_name}'
logging.info(f'{__file__} - {msg}')
return f'{msg}'
except HttpResponseError as e:
msg = f'Unexpected error : {e.message}'
logging.info(f'{__file__} - {msg}')
return msg
# Grab the Azure Credentials needed.
subscription_id = config.get('azure_credentials', 'azure_subscription_id')
tenant_id = config.get('azure_credentials', 'azure_tenant_id')
client_id = config.get('azure_credentials', 'azure_client_id')
client_secret = config.get('azure_credentials', 'azure_client_secret')
# Define the Credentials.
credential = ServicePrincipalCredentials(
tenant=tenant_id,
client_id=client_id,
secret=client_secret
)
# Initialize the SQL Management Client.
sql_management_client = SqlManagementClient(
credentials=credential,
subscription_id=subscription_id
)
# Grab a server using the Resource Group Name.
server: Server = sql_management_client.servers.get(
resource_group_name='sigma-coding-tutorials',
server_name='trading-robot'
)
print(server.as_dict())
# Grab the master database from the `trading-robot` server.
database: Database = sql_management_client.databases.get(
resource_group_name='sigma-coding-tutorials',
server_name='trading-robot',
database_name='master'
)
def main():
SUBSCRIPTION_ID = os.environ.get("SUBSCRIPTION_ID", None)
PASSWORD = os.environ.get("PASSWORD", None)
GROUP_NAME = "testgroupx"
VIRTUAL_NETWORK_RULE = "virtual_network_rulexxyyzz"
SERVER = "serverxxy"
NETWORK = "networkxxy"
SUBNET = "subnetxxy"
# Create client
# # For other authentication approaches, please see: https://pypi.org/project/azure-identity/
resource_client = ResourceManagementClient(
credential=DefaultAzureCredential(), subscription_id=SUBSCRIPTION_ID)
sql_client = SqlManagementClient(credential=DefaultAzureCredential(),
subscription_id=SUBSCRIPTION_ID)
network_client = NetworkManagementClient(
credential=DefaultAzureCredential(), subscription_id=SUBSCRIPTION_ID)
# Create resource group
resource_client.resource_groups.create_or_update(GROUP_NAME,
{"location": "eastus"})
# - init depended resources -
# Create Server
server = sql_client.servers.begin_create_or_update(
GROUP_NAME, SERVER, {
"location": "eastus",
"administrator_login": "******",
"administrator_login_password": PASSWORD
}).result()
print("Create server:\n{}".format(server))
# Create virtual network
network = network_client.virtual_networks.begin_create_or_update(
GROUP_NAME, NETWORK, {
'location': "eastus",
'address_space': {
'address_prefixes': ['10.0.0.0/16']
}
}).result()
print("Create virtual network:\n{}".format(network))
# Create subnet
subnet = network_client.subnets.begin_create_or_update(
GROUP_NAME, NETWORK, SUBNET, {
'address_prefix': '10.0.0.0/24',
}).result()
print("Create subnet:\n{}".format(subnet))
# - end -
# Create virtual network rule
virtual_network_rule = sql_client.virtual_network_rules.begin_create_or_update(
GROUP_NAME, SERVER, VIRTUAL_NETWORK_RULE, {
"ignore_missing_vnet_service_endpoint":
True,
"virtual_network_subnet_id":
"/subscriptions/" + SUBSCRIPTION_ID + "/resourceGroups/" +
GROUP_NAME + "/providers/Microsoft.Network/virtualNetworks/" +
NETWORK + "/subnets/" + SUBNET
}).result()
print("Create virtual network rule:\n{}".format(virtual_network_rule))
# Get virtual network rule
virtual_network_rule = sql_client.virtual_network_rules.get(
GROUP_NAME, SERVER, VIRTUAL_NETWORK_RULE)
print("Get virtual network rule:\n{}".format(virtual_network_rule))
# Delete virtual network rule
virtual_network_rule = sql_client.virtual_network_rules.begin_delete(
GROUP_NAME, SERVER, VIRTUAL_NETWORK_RULE).result()
print("Delete virtual network rule.\n")
# Delete Group
resource_client.resource_groups.begin_delete(GROUP_NAME).result()
def main():
SUBSCRIPTION_ID = os.environ.get("SUBSCRIPTION_ID", None)
PASSWORD = os.environ.get("PASSWORD", None)
GROUP_NAME = "testgroupx"
VULNERABILITY_ASSESSMENT = "vulnerabilityassessmentxxyyzz"
SECURITY_ALERT_POLICY_NAME = "securityalertpolicy"
SERVER = "serverxxy"
ACCOUNT = "accountxxy"
CONTAINER = "containerxxy"
# Create client
# # For other authentication approaches, please see: https://pypi.org/project/azure-identity/
resource_client = ResourceManagementClient(
credential=DefaultAzureCredential(), subscription_id=SUBSCRIPTION_ID)
sql_client = SqlManagementClient(credential=DefaultAzureCredential(),
subscription_id=SUBSCRIPTION_ID)
# - init depended client -
storage_client = StorageManagementClient(
credential=DefaultAzureCredential(), subscription_id=SUBSCRIPTION_ID)
# - end -
# Create resource group
resource_client.resource_groups.create_or_update(GROUP_NAME,
{"location": "eastus"})
# - init depended resources -
# Create storage account
storageaccount = storage_client.storage_accounts.begin_create(
GROUP_NAME, ACCOUNT, {
"sku": {
"name": "Standard_GRS"
},
"kind": "StorageV2",
"location": "eastus",
"encryption": {
"services": {
"file": {
"key_type": "Account",
"enabled": True
},
"blob": {
"key_type": "Account",
"enabled": True
}
},
"key_source": "Microsoft.Storage"
},
"tags": {
"key1": "value1",
"key2": "value2"
}
}).result()
print("Create storage account:\n{}".format(storageaccount))
# Create blob container
container = storage_client.blob_containers.create(GROUP_NAME, ACCOUNT,
CONTAINER, {})
print("Create blob container:\n{}".format(container))
# Regenerate storage account key
key = storage_client.storage_accounts.regenerate_key(
GROUP_NAME, ACCOUNT, {"key_name": "key2"})
ACCESS_KEY = key.keys[0].value
print("Generate key:\n{}".format(key))
# Create Server
server = sql_client.servers.begin_create_or_update(
GROUP_NAME, SERVER, {
"location": "eastus",
"administrator_login": "******",
"administrator_login_password": PASSWORD
}).result()
print("Create server:\n{}".format(server))
# Create server security alert policy
policy = sql_client.server_security_alert_policies.begin_create_or_update(
GROUP_NAME, SERVER, SECURITY_ALERT_POLICY_NAME, {
"state": "Enabled",
"email_account_admins": True,
"disabled_alerts": [],
"email_addresses": []
}).result()
print("Create server security alert policy:\n{}".format(policy))
# - end -
# Create vulnerability assessment
vulnerability_assessment = sql_client.server_vulnerability_assessments.create_or_update(
GROUP_NAME, SERVER, VULNERABILITY_ASSESSMENT, {
"storage_container_path":
"https://" + ACCOUNT + ".blob.core.windows.net/" + CONTAINER + "/",
"storage_account_access_key":
ACCESS_KEY
})
print("Create vulnerability assessment:\n{}".format(
vulnerability_assessment))
# Get vulnerability assessment
vulnerability_assessment = sql_client.server_vulnerability_assessments.get(
GROUP_NAME, SERVER, VULNERABILITY_ASSESSMENT)
print("Get vulnerability assessment:\n{}".format(vulnerability_assessment))
# Delete vulnerability assessment
vulnerability_assessment = sql_client.server_vulnerability_assessments.delete(
GROUP_NAME, SERVER, VULNERABILITY_ASSESSMENT)
print("Delete vulnerability assessment.\n")
# Delete Group
resource_client.resource_groups.begin_delete(GROUP_NAME).result()