问题描述
我正在尝试创建SQL Server的新Azure实例，然后在其中创建一些新数据库.
I am trying to create a new Azure instance of SQL Server in which I would like to then create a few new databases.
我从Azure门户知道，某种管理员用户可能是:
I know from the Azure Portal that some sort of admin users could be:
- 一个 SA 用户(我认为这是"Server Admin"的意思，它看起来像是管理SQL Server实例的某种旧方法，但同时又非常基础"，并且证明了工作) Active Directory用户(在此处不确定Azure术语，但似乎可能是整个Azure平台的广泛用户"，例如我自己的Azure Portal登录用户，这不特定于数据库)世界).
- an SA user (I think this means "Server Admin" and it looks like some sort of old way of managing a SQL Server instance, but at the same time very "basic" and proved to work)
- an Active Directory user (not sure about Azure terminology here, but it looks like this could be some "broad user" for the whole Azure platform, like e.g. my own login user for the Azure Portal, this is not specific to databases world).
我想创建一个具有SA用户的SQL Server来管理服务器. 在Azure门户中，我找不到为SQL Server实例的SA用户生成ARM模板的方法.
I would like to create a SQL Server with a SA user to administer the server. From the Azure portal I can not find a way to generate an ARM template for a SA user for the SQL Server instance.
我正在从10000行的ARM模板中复制粘贴，以获取非常长的SQL服务器和数据库列表，但是我无法隔离基本步骤，以期获得希望简洁且简短的ARM模板.
I am copy pasting from a 10000 lines ARM template for a very long list of SQL servers and databases but I am not able to isolate the basic steps to have a hopefully clean and short ARM template to start with.
这是我正在尝试在Azure上部署的ARM模板:
This is the ARM template I am trying to deploy on Azure:
{
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"location": {
"type": "string",
"defaultValue": "westeurope"
},
"foo_sql_server_name": {
"defaultValue": "foo-sql-server",
"type": "String"
}
},
"resources": [
{
"type": "Microsoft.Sql/servers",
"kind": "v12.0",
"name": "[parameters('foo_sql_server_name')]",
"apiVersion": "2015-05-01-preview",
"location": "[parameters('location')]",
"scale": null,
"properties": {
"administratorLogin": "<MY_SA_USER_THAT_I_CAN_NOT_CREATE>",
"version": "12.0"
},
"dependsOn": []
}
]
}
使用以下命令运行上述内容时
When running the above with:
az group deployment create \
--name "deployDBs" \
--resource-group "MyCustomResourceGroup" \
--template-file ./templates/db.json # --verbose --debug
然后我收到以下错误消息:
Then I get the following error message:
Deployment failed. Correlation ID: <A_CUSTOM_GUID>. {
"status": "Failed",
"error": {
"code": "ResourceDeploymentFailure",
"message": "The resource operation completed with terminal provisioning state 'Failed'.",
"details": [
{
"code": "InvalidParameterValue",
"message": "Invalid value given for parameter Password. Specify a valid parameter value."
}
]
}
}
当删除JSON字段administratorLogin时(因为希望我可以以某种方式在尚未确定的其他地方创建SA用户)，然后收到以下错误消息:
When removing the JSON field administratorLogin (because hopefully I could create the SA user somehow somewhere else that I yet have to figure out), then I get the following error message:
Deployment failed. Correlation ID: <ANOTHER_CUSTOM_GUID>. {
"status": "Failed",
"error": {
"code": "ResourceDeploymentFailure",
"message": "The resource operation completed with terminal provisioning state 'Failed'.",
"details": [
{
"code": "InvalidParameterValue",
"message": "Invalid value given for parameter Login. Specify a valid parameter value."
}
]
}
}
我无法从10000行自动生成的ARM模板中找到SA用户(服务器管理员)的用户名密码"对的定义.
I am not able to find the definition for the pair "username password" for the SA user (Server Admin) from the 10000 lines auto-generated ARM template.
在部署SQL Server的新实例时，如何为SQL Server创建/注入SA用户?
How could I create/inject a SA user for the SQL Server while deploying a new instance of a SQL Server?
推荐答案
在本地SQL Server实例上使用的sa登录在Azure SQL上称为Admin登录.您可以提供管理员登录名及其密码作为参数，如以下示例模板所示:
The sa login you use on an on-premises SQL Server instance is known on Azure SQL as the Admin login. You can provide the name of the admin login and its password as parameter as shown on below sample template:
{
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"yourservernameName": {
"type": "string",
"defaultValue": "yourservername2"
},
"yourservernameAdminLogin": {
"type": "string",
"defaultValue": "VeryWiseAdmin",
"minLength": 1
},
"yourservernameAdminLoginPassword": {
"type": "securestring",
"defaultValue": "ReplaceWithTheMostSecurePasswordThatEverExisted&NeverShareLikeThisWithAnyone!"
},
"dbnameName": {
"type": "string",
"defaultValue": "dbname",
"minLength": 1
},
"dbnameCollation": {
"type": "string",
"minLength": 1,
"defaultValue": "SQL_Latin1_General_CP1_CI_AS"
},
"dbnameEdition": {
"type": "string",
"defaultValue": "Basic"
},
"dbnameRequestedServiceObjectiveName": {
"type": "string",
"defaultValue": "Basic"
}
},
"variables": {
},
"resources": [
{
"name": "[parameters('yourservernameName')]",
"type": "Microsoft.Sql/servers",
"location": "West Europe",
"apiVersion": "2014-04-01-preview",
"dependsOn": [],
"tags": {
"displayName": "yourservername"
},
"properties": {
"administratorLogin": "[parameters('yourservernameAdminLogin')]",
"administratorLoginPassword": "[parameters('yourservernameAdminLoginPassword')]",
"version": "12.0"
},
"resources": [
{
"name": "[concat(parameters('yourservernameName'),'/AllowAllWindowsAzureIps')]",
"type": "Microsoft.Sql/servers/firewallRules",
"location": "[resourceGroup().location]",
"apiVersion": "2014-04-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('yourservernameName'))]"
],
"properties": {
"startIpAddress": "0.0.0.0",
"endIpAddress": "0.0.0.0"
}
},
{
"name": "[concat(parameters('yourservernameName'),'/',parameters('dbnameName'))]",
"type": "Microsoft.Sql/servers/databases",
"location": "West Europe",
"apiVersion": "2014-04-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('yourservernameName'))]"
],
"tags": {
"displayName": "dbname"
},
"properties": {
"collation": "[parameters('dbnameCollation')]",
"edition": "[parameters('dbnameEdition')]",
"maxSizeBytes": "1073741824",
"requestedServiceObjectiveName": "[parameters('dbnameRequestedServiceObjectiveName')]"
}
}
]
}
],
"outputs": {
"SomeString": {
"type": "string",
"value": "What ever you want to put here"
},
"ServerNameParam": {
"type": "string",
"value": "[parameters('yourservernameName')]"
},
"ServerResourceID": {
"type": "string",
"value": "[resourceId('Microsoft.Sql/servers', parameters('yourservernameName'))]"
},
"ServerObject": {
"type": "object",
"value": "[reference(parameters('yourservernameName'))]"
},
"SqlServerURL": {
"type": "string",
"value": "[reference(parameters('yourservernameName')).fullyQualifiedDomainName]"
},
"DbResourceID": {
"type": "string",
"value": "[resourceId('Microsoft.Sql/servers/databases', parameters('yourservernameName'), parameters('dbnameName'))]"
},
"DbObject": {
"type": "object",
"value": "[reference(parameters('dbnameName'))]"
},
"DbAdoConnString": {
"type": "string",
"value": "[concat('Server=tcp:',reference(parameters('yourservernameName')).fullyQualifiedDomainName,',1433;Initial Catalog=',parameters('dbnameName'),';Persist Security Info=False;User ID=',reference(parameters('yourservernameName')).administratorLogin,';Password=',reference(parameters('yourservernameName')).administratorLoginPassword,';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;')]"
}
}
}
这篇关于通过Azure资源管理器(ARM)模板创建SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！