1# -------------------------------------------------------------------------------------------- 2# Copyright (c) Microsoft Corporation. All rights reserved. 3# Licensed under the MIT License. See License.txt in the project root for license information. 4# -------------------------------------------------------------------------------------------- 5# pylint: disable=unused-argument 6from azure.cli.core.util import sdk_no_wait 7from azure.mgmt.synapse.models import SqlPool, SqlPoolPatchInfo, Sku 8from knack.util import CLIError 9from .._client_factory import cf_synapse_client_workspace_factory 10from ..constant import SynapseSqlCreateMode, SqlPoolConnectionClientAuthenticationType, SqlPoolConnectionClientType 11 12 13# Synapse sqlpool 14def create_sql_pool(cmd, client, resource_group_name, workspace_name, sql_pool_name, performance_level, tags=None, 15 no_wait=False): 16 workspace_client = cf_synapse_client_workspace_factory(cmd.cli_ctx) 17 workspace_object = workspace_client.get(resource_group_name, workspace_name) 18 location = workspace_object.location 19 20 sku = Sku(name=performance_level) 21 22 sql_pool_info = SqlPool(sku=sku, location=location, create_mode=SynapseSqlCreateMode.Default, tags=tags) 23 24 return sdk_no_wait(no_wait, client.begin_create, resource_group_name, workspace_name, sql_pool_name, sql_pool_info) 25 26 27def update_sql_pool(cmd, client, resource_group_name, workspace_name, sql_pool_name, sku_name=None, tags=None): 28 sku = Sku(name=sku_name) 29 sql_pool_patch_info = SqlPoolPatchInfo(sku=sku, tags=tags) 30 return client.update(resource_group_name, workspace_name, sql_pool_name, sql_pool_patch_info) 31 32 33def restore_sql_pool(cmd, client, resource_group_name, workspace_name, sql_pool_name, destination_name, 34 performance_level=None, restore_point_in_time=None, source_database_deletion_date=None, 35 no_wait=False, **kwargs): 36 """ 37 Restores an existing or deleted SQL pool (i.e. create with 'Restore' 38 or 'PointInTimeRestore' create mode.) 39 40 Custom function makes create mode more convenient. 41 """ 42 if not (restore_point_in_time or source_database_deletion_date): 43 raise CLIError('Either --time or --deleted-time must be specified.') 44 45 # Set create mode properties 46 is_deleted = source_database_deletion_date is not None 47 create_mode = SynapseSqlCreateMode.Restore if is_deleted else SynapseSqlCreateMode.PointInTimeRestore 48 49 source_sql_pool_info = client.get(resource_group_name, workspace_name, sql_pool_name) 50 51 # get the default performance_level 52 if performance_level is None: 53 performance_level = source_sql_pool_info.sku.name 54 55 # create source database id 56 source_database_id = _construct_database_resource_id(cmd.cli_ctx, resource_group_name, workspace_name, 57 sql_pool_name) 58 59 sku = Sku(name=performance_level) 60 dest_sql_pool_info = SqlPool(sku=sku, location=source_sql_pool_info.location, create_mode=create_mode, 61 restore_point_in_time=restore_point_in_time, source_database_id=source_database_id) 62 63 return sdk_no_wait(no_wait, client.begin_create, resource_group_name, workspace_name, destination_name, 64 dest_sql_pool_info) 65 66 67def sql_pool_show_connection_string( 68 cmd, 69 client_provider, 70 sql_pool_name='<sql pool name>', 71 workspace_name='<workspace name>', 72 auth_type=SqlPoolConnectionClientAuthenticationType.SqlPassword.value): 73 """ 74 Builds a SQL connection string for a specified client provider. 75 """ 76 77 workspace_sql_pool_compute_suffix = cmd.cli_ctx.cloud.suffixes.synapse_analytics_endpoint.replace('dev', 'sql') 78 79 conn_str_props = { 80 'workspace': workspace_name, 81 'workspace_fqdn': '{}{}'.format(workspace_name, workspace_sql_pool_compute_suffix), 82 'workspace_suffix': workspace_sql_pool_compute_suffix, 83 'sql_pool': sql_pool_name 84 } 85 86 formats = { 87 SqlPoolConnectionClientType.AdoDotNet: { 88 SqlPoolConnectionClientAuthenticationType.SqlPassword: 89 'Server=tcp:{workspace_fqdn},1433;Initial Catalog={sql_pool};Persist Security Info=False;' 90 'User ID=<username>;Password=<password>;MultipleActiveResultSets=False;Encrypt=True;' 91 'TrustServerCertificate=False;Connection Timeout=30;', 92 SqlPoolConnectionClientAuthenticationType.ActiveDirectoryPassword: 93 'Server=tcp:{workspace_fqdn},1433;Initial Catalog={sql_pool};Persist Security Info=False;' 94 'User ID=<username>;Password=<password>;MultipleActiveResultSets=False;Encrypt=True;' 95 'TrustServerCertificate=False;Authentication="Active Directory Password";', 96 SqlPoolConnectionClientAuthenticationType.ActiveDirectoryIntegrated: 97 'Server=tcp:{workspace_fqdn},1433;Initial Catalog={sql_pool};Persist Security Info=False;' 98 'User ID=<username>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;' 99 'Authentication="Active Directory Integrated";' 100 }, 101 SqlPoolConnectionClientType.Jdbc: { 102 SqlPoolConnectionClientAuthenticationType.SqlPassword: 103 'jdbc:sqlserver://{workspace_fqdn}:1433;database={sql_pool};user=<username>@{workspace};' 104 'password=<password>;encrypt=true;trustServerCertificate=false;' 105 'hostNameInCertificate=*{workspace_suffix};loginTimeout=30;', 106 SqlPoolConnectionClientAuthenticationType.ActiveDirectoryPassword: 107 'jdbc:sqlserver://{workspace_fqdn}:1433;database={sql_pool};user=<username>;' 108 'password=<password>;encrypt=true;trustServerCertificate=false;' 109 'hostNameInCertificate=*{workspace_suffix};loginTimeout=30;authentication=ActiveDirectoryPassword', 110 SqlPoolConnectionClientAuthenticationType.ActiveDirectoryIntegrated: 111 'jdbc:sqlserver://{workspace_fqdn}:1433;database={sql_pool};' 112 'encrypt=true;trustServerCertificate=false;' 113 'hostNameInCertificate=*{workspace_suffix};loginTimeout=30;Authentication=ActiveDirectoryIntegrated', 114 }, 115 SqlPoolConnectionClientType.Odbc: { 116 SqlPoolConnectionClientAuthenticationType.SqlPassword: 117 'Driver={{ODBC Driver 13 for SQL Server}};Server=tcp:{workspace_fqdn},1433;' 118 'Database={sql_pool};Uid=<username>;Pwd=<password>;Encrypt=yes;' 119 'TrustServerCertificate=no;Connection Timeout=30;', 120 SqlPoolConnectionClientAuthenticationType.ActiveDirectoryPassword: 121 'Driver={{ODBC Driver 13 for SQL Server}};Server=tcp:{workspace_fqdn},1433;' 122 'Database={sql_pool};Uid=<username>;Pwd=<password>;Encrypt=yes;' 123 'TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword', 124 SqlPoolConnectionClientAuthenticationType.ActiveDirectoryIntegrated: 125 'Driver={{ODBC Driver 13 for SQL Server}};Server=tcp:{workspace_fqdn},1433;' 126 'Database={sql_pool};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;' 127 'Authentication=ActiveDirectoryIntegrated', 128 }, 129 SqlPoolConnectionClientType.Php: { 130 # pylint: disable=line-too-long 131 SqlPoolConnectionClientAuthenticationType.SqlPassword: 132 '$connectionOptions = array("UID"=>"<username>@{workspace}", "PWD"=>"<password>", "Database"=>{sql_pool}, "LoginTimeout" => 30, "Encrypt" => 1, "TrustServerCertificate" => 0); $serverName = "tcp:{workspace_fqdn},1433"; $conn = sqlsrv_connect($serverName, $connectionOptions);', 133 SqlPoolConnectionClientAuthenticationType.ActiveDirectoryPassword: 134 CLIError('PHP sqlsrv driver only supports SQL Password authentication.'), 135 SqlPoolConnectionClientAuthenticationType.ActiveDirectoryIntegrated: 136 CLIError('PHP sqlsrv driver only supports SQL Password authentication.'), 137 }, 138 SqlPoolConnectionClientType.PhpPdo: { 139 # pylint: disable=line-too-long 140 SqlPoolConnectionClientAuthenticationType.SqlPassword: 141 '$conn = new PDO("sqlsrv:server = tcp:{workspace_fqdn},1433; Database = {sql_pool}; LoginTimeout = 30; Encrypt = 1; TrustServerCertificate = 0;", "<username>", "<password>");', 142 SqlPoolConnectionClientAuthenticationType.ActiveDirectoryPassword: 143 CLIError('PHP Data Object (PDO) driver only supports SQL Password authentication.'), 144 SqlPoolConnectionClientAuthenticationType.ActiveDirectoryIntegrated: 145 CLIError('PHP Data Object (PDO) driver only supports SQL Password authentication.'), 146 } 147 } 148 149 f = formats[client_provider][auth_type] 150 151 if isinstance(f, Exception): 152 # Error 153 raise f 154 155 # Success 156 return f.format(**conn_str_props) 157 158 159def _construct_database_resource_id(cli_ctx, resource_group_name, server_name, database_name): 160 # url parse package has different names in Python 2 and 3. 'six' package works cross-version. 161 from six.moves.urllib.parse import quote # pylint: disable=import-error 162 from azure.cli.core.commands.client_factory import get_subscription_id 163 164 return '/subscriptions/{}/resourceGroups/{}/providers/Microsoft.Sql/servers/{}/databases/{}'.format( 165 quote(get_subscription_id(cli_ctx)), 166 quote(resource_group_name), 167 quote(server_name), 168 quote(database_name)) 169