无法使用SMO在SQL Azure数据库中创建表 [英] Failure to CREATE TABLE in SQL Azure database using SMO

问题描述

由于SQL Azure不支持 USE [dbname] ，因此我试图通过在连接字符串中指定数据库名称然后执行 CREATE TABLE来连接到我的SQL Azure数据库... 脚本.但是，此操作失败，并出现System.Data.SqlClient.SqlException 数据库'master'中的CREATE TABLE权限被拒绝.'.在这里，我试图对master执行此语句，这是我做错了吗?

这是示例C#代码:

字符串连接字符串= @数据源= tcp:MYSERVER.database.windows.net;初始目录= MYDATABASE;集成安全性= False;用户ID = USER @ MYSERVER;密码= PWD;连接超时= 60;加密=真; TrustServerCertificate =真"； 
使用(SqlConnection连接=新的SqlConnection(connectionString)){connection.Open();ServerConnection serverConnection =新的ServerConnection(连接)；服务器服务器=新服务器(serverConnection);server.ConnectionContext.ExecuteNonQuery("CREATE TABLE New(NewId int)");}

解决方案

事实证明，我的代码存在多个问题.对于SQL Azure，SMO仍然很古怪.因此，发布我的发现，以防有人走过同样的坎road之路.

1. 每当您获得这样的数据库对象时，SMO都会在后台切换默认数据库:

    数据库database = server.Databases [databaseName] 

   初始目录成为主目录，您可以在 server.ConnectionContext.ConnectionString 中更改的连接字符串中看到它(常规" SQL不会这样做).解决方案是每次将数据库切换到Master时都打开一个新的连接(并关闭旧的连接)，因为一旦建立连接就无法更改数据库名称(显然，只有SQL Azure可以做到).

2. 有时结合第一个问题，无法打开用来初始化ServerConnection和Server的连接.这将给出一个模糊的错误消息，指出登录失败，并给出向导和时间戳，要求与客户支持联系.废话.解决此问题的方法是不打开连接，并让ServerConnection在Server对象初始化期间打开它:
3. 最终，SQL Azure on Server对象不喜欢Alter().删除了所有的Alter.

所以最终的代码片段看起来像这样:

 字符串连接字符串="Server = tcp:XXXXX.database.windows.net;数据库= XXXXXX;用户ID = XXXXXX;密码= XXXXX; Trusted_Connection = False;加密=真; trustservercertificate = true";SqlConnection连接=新的SqlConnection(connectionString);//不显式打开连接，它将在服务器初始化时打开//connection.Open();ServerConnection serverConnection =新的ServerConnection(连接)；服务器服务器=新服务器(serverConnection);//在此行之后，默认数据库将切换为Master数据库database = server.Databases ["MyDatabase"];//您仍然可以使用此数据库对象和服务器连接来//针对此数据库执行某些操作，例如添加数据库角色//和用户DatabaseRole角色=新的DatabaseRole(数据库，"NewRole")；role.Create();//如果您要针对此数据库执行脚本，则必须打开//另一个连接并重新初始化服务器对象server.ConnectionContext.Disconnect();connection =新的SqlConnection(connectionString);serverConnection =新的ServerConnection(连接);服务器=新服务器(serverConnection);server.ConnectionContext.ExecuteNonQuery("CREATE TABLE New(NewId int)"); 

如果有人感兴趣，这是第二点的晦涩例外:

 未处理Microsoft.SqlServer.Management.Common.ConnectionFailureException消息=无法连接到服务器.源= Microsoft.SqlServer.Smo堆栈跟踪:在Microsoft.SqlServer.Management.Smo.DatabaseCollection.get_Item(字符串名称)InnerException:System.Data.SqlClient.SqlException消息=用户"XXXXXXXX"的登录失败.此会话已分配了跟踪ID"XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXX".需要帮助时，将此跟踪ID提供给客户支持.Source = .Net SqlClient数据提供程序错误代码= -2146232060等级= 14LineNumber = 65536数= 18456程序="服务器= tcp:XXXXXXXX.database.windows.net状态= 1堆栈跟踪:在System.Data.SqlClient.SqlInternalConnection.OnError(SqlException异常，布尔breakConnection)在System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()在System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj)在System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(布尔enlistOK)在System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin处(ServerInfo serverInfo，字符串newPassword，布尔值ignoreSniOpenTimeout，TimeoutTimer超时，SqlConnection owningObject)在System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover处(ServerInfo serverInfo，字符串newPassword，布尔值redirectedUserInstance，SqlConnection owningObject，SqlConnectionString connectionOptions，TimeoutTimer超时)在System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject，TimeoutTimer超时，SqlConnectionString connectionOptions，String newPassword，布尔型redirectedUserInstance)在System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity身份，SqlConnectionString connectionOptions，对象providerInfo，字符串newPassword，SqlConnection owningObject，布尔型redirectedUserInstance)在System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions选项，对象poolGroupProviderInfo，DbConnectionPool池，DbConnection owningConnection)在System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection，DbConnectionPool池，DbConnectionOptions选项)在System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)在System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)在System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)在System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)在System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection externalConnection，DbConnectionFactory connectionFactory)在System.Data.SqlClient.SqlConnection.Open()在Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)在Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() 

Since USE [dbname] is not supported in SQL Azure, I'm trying to connect to my SQL Azure database by specifying the database name in the connection string and then executing CREATE TABLE... script. However, this fails with System.Data.SqlClient.SqlException "CREATE TABLE permission denied in database 'master'." What am I doing wrong here that it is trying to execute this statement against master?

Here is a sample C# code:

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)");
}

解决方案

It turns out there were multiple problems with the code I had. SMO is still pretty wacky when it comes to SQL Azure. So, posting my findings in case somebody is going through the same rocky road.

1. SMO switches the default database behind the scenes every time you get a database object like this:

   
   Database database = server.Databases[databaseName]
   

   Initial catalog becomes Master and you can see it in the connection string that changes in server.ConnectionContext.ConnectionString ("normal" SQL doesn’t do it). Resolution for this was to open a fresh connection (and close the old one) every time the database is switched to Master, since the database name cannot be changed once the connection is established (apparently, only SQL Azure can do it).

2. Opening a connection, with which ServerConnection and Server are initialized, would fail sometimes in conjunction with the first problem. This would give an obscure error message stating that the login failed giving a guid and timestamp asking to contact customer support. Nonsense. The resolution for this problem was to not open the connection and let ServerConnection open it during initialization of the Server object:
3. Finally, Alter() is not liked by SQL Azure on Server object. Removed all Alter's.

So the final code snippet looks something like this:

       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)");

And here is the obscure exception for point #2 in case somebody is interested:

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()

这篇关于无法使用SMO在SQL Azure数据库中创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！