Failure to CREATE TABLE in SQL Azure database using SMO

1
votes

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

3
c#smoazure-sql-database
Have you tried using SqlCommand as shown in this example msdn.microsoft.com/en-us/library/ee336243.aspx ? – Bala R
I get the same error with SqlCommand – kateroh

3 Answers

10
votes

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

Just a wild guess, based on a connect string that happens to work for me.

Have you tried using

Database=MYDATABASE

in place of

Initial Catalog=MYDATABASE

in the connect string?

0
votes

And one more thing that helped me- ServerConnection should be disconnected for each operation.

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