使用SMO在SQL Azure数据库中创建表失败

本文关键字：创建 失败 数据库 Azure SMO SQL 使用 | 更新日期: 2023-09-27 17:53:00

由于SQL Azure不支持USE [dbname]，我试图通过指定连接字符串中的数据库名称连接到我的SQL Azure数据库，然后执行CREATE TABLE...脚本。但是，如果出现System.Data.SqlClient.SqlException "CREATE TABLE permission denied in database 'master' "，就会失败。我在这里做错了什么，它试图对master执行这个语句?

下面是一个c#示例代码:
string connectionString = @"Data Source=tcp:MYSERVER.database.windows.net;Initial Catalog=MYDATABASE;Integrated Security=False;User ID=USER@MYSERVER;Password=PWD;Connect Timeout=60;Encrypt=True;TrustServerCertificate=True";


using (SqlConnection connection = new SqlConnection(connectionString))
{
   connection.Open();
   ServerConnection serverConnection = new ServerConnection(connection);
   Server server = new Server(serverConnection);
   server.ConnectionContext.ExecuteNonQuery("CREATE TABLE New (NewId int)");
}

使用SMO在SQL Azure数据库中创建表失败

原来我的代码有多个问题。当涉及到SQL Azure时，SMO仍然相当古怪。所以，把我的发现贴出来，以防有人也在经历同样的困难。

  1. SMO在后台切换默认数据库，每次你得到一个这样的数据库对象:
    
Database database = server.Databases[databaseName]
    初始目录变为Master，您可以在server.ConnectionContext中更改的连接字符串中看到它。ConnectionString("正常"SQL不这样做)。解决这个问题的方法是每次数据库切换到Master时打开一个新的连接(并关闭旧的连接)，因为一旦连接建立，数据库名称就不能更改(显然，只有SQL Azure可以这样做)。
  2. 打开一个初始化ServerConnection和Server的连接，有时会与第一个问题一起失败。这将给出一个模糊的错误消息，说明登录失败，并给出一个向导和时间戳，要求联系客户支持。无稽之谈。解决这个问题的方法是不打开连接，让ServerConnection在初始化Server对象时打开它:
  3. 最后，Alter()不受SQL Azure on Server对象的欢迎。移除所有Alter。
所以最后的代码片段看起来像这样: 

       string connectionString = "Server=tcp:XXXXX.database.windows.net;Database=XXXXXX;User ID=XXXXXX;Password=XXXXX;Trusted_Connection=False;Encrypt=True;trustservercertificate=true";
       SqlConnection connection = new SqlConnection(connectionString);
       // do not explicitly open connection, it will be opened when Server is initialized
       // connection.Open();
       ServerConnection serverConnection = new ServerConnection(connection);
       Server server = new Server(serverConnection);
       // after this line, the default database will be switched to Master
       Database database = server.Databases["MyDatabase"];
       // you can still use this database object and server connection to 
       // do certain things against this database, like adding database roles 
       // and users      
       DatabaseRole role = new DatabaseRole(database, "NewRole");
       role.Create();
       // if you want to execute a script against this database, you have to open 
       // another connection and re-initiliaze the server object
       server.ConnectionContext.Disconnect();
       connection = new SqlConnection(connectionString);
       serverConnection = new ServerConnection(connection);
       server = new Server(serverConnection);
       server.ConnectionContext.ExecuteNonQuery("CREATE TABLE New (NewId int)");

如果有人感兴趣的话，这里是第2点的一个模糊的例外:


Microsoft.SqlServer.Management.Common.ConnectionFailureException was unhandled
  Message=Failed to connect to server .
  Source=Microsoft.SqlServer.Smo
  StackTrace:
       at Microsoft.SqlServer.Management.Smo.DatabaseCollection.get_Item(String name)
       InnerException: System.Data.SqlClient.SqlException
       Message=Login failed for user 'XXXXXXXX'.
       This session has been assigned a tracing ID of 'XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXX'.  Provide this tracing ID to customer support when you need assistance.
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=14
       LineNumber=65536
       Number=18456
       Procedure=""
       Server=tcp:XXXXXXXX.database.windows.net
       State=1
       StackTrace:
            at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
            at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
            at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
            at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)
            at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
            at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
            at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
            at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
            at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
            at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
            at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
            at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
            at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
            at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
            at System.Data.SqlClient.SqlConnection.Open()
            at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
            at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

只是一个大胆的猜测，基于一个碰巧为我工作的连接字符串。

你试过使用

吗?
数据库= MYDATABASE

代替

初始目录= MYDATABASE

中的连接字符串?

还有一件事对我有帮助-每次操作都应该断开ServerConnection。

using (var connection = new SqlConnection(parameters.ConnectionStringToMasterDatabase))
            {
                var serverConnection = new ServerConnection(connection);
                try
                {
                    var server = new Server(serverConnection);
                    // do something
                }
                finally
                {
                    serverConnection.Disconnect();
                }
            }
using (var connection = new SqlConnection(parameters.ConnectionStringToMasterDatabase))
            {
                var serverConnection = new ServerConnection(connection);
                try
                {
                    var server = new Server(serverConnection);
                    // do something else
                }
                finally
                {
                    serverConnection.Disconnect();
                }
            }
相关文章：