Cannot connect to SQL Azure from local machine

Question

I have made a simple SQL generator XAML application based on Fluent NHibernate that allows me to generate the DDL to run onto database. With a local MySQL setup it works fine.

I want now to run it onto SQL Azure.

• I have created a website that will host the NH application on Management Portal. Check!
• I have created the database used by the NH application. Check!
• I have set up a firewall rule to allow my IP address to connect to SQL Azure. Check!
• I have tried to connect to Azure's official DB management portal and logged in with my credentials. Check!
• I have tried to run the NHibernate schema generator with the correct connection string (double-checked the password) but I had an error. Boom!

The error(anonymised) is the following

---------------------------

---------------------------
Cannot open database "[MyDb]" requested by the login. The login failed.

Login failed for user 'mydblogin'.

This session has been assigned a tracing ID of '{{guid}}'.  Provide this tracing ID to customer support when you need assistance.
---------------------------
OK   
---------------------------

Code:

PersistenceConfigurer persistenceConfigurer = MsSqlConfiguration.MsSql2008
    .Dialect<MsSqlAzure2008Dialect>()
    .Driver<SqlClientDriver>()
    .FormatSql()
    .ShowSql()
    .ConnectionString(connectionString);

FluentConfiguration fc = Fluently.Configure()
    .Database(persistenceConfigurer)
    .ExposeConfiguration(
        cfg => cfg.SetProperty("hbm2ddl.keywords", "auto-quote")
                    .SetProperty("hbm2ddl.auto", "none"))
    .Mappings(
    m => m.FluentMappings.AddFromAssemblyOf<NHibernateFactory>()
    .Conventions.AddFromAssemblyOf<NHibernateFactory>());
Configuration ret = fc.BuildConfiguration();
SchemaMetadataUpdater.QuoteTableAndColumns(ret);

Connection string (obfuscated)

Server=tcp:dbhost.database.windows.net,1433;Database=[MyDb];User ID=login@dbhost;Password=password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;

Exception is System.Data.SqlClient.SqlException (0x80131904)

Stack trace

   in System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   in System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   in System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   in System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   in System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   in System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   in System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   in System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions)
   in System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   in System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   in System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionOptions userOptions)
   in System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionOptions userOptions)
   in System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   in System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   in System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   in System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   in System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   in System.Data.SqlClient.SqlConnection.Open()
   in NHibernate.Connection.DriverConnectionProvider.GetConnection()
   in NHibernate.Tool.hbm2ddl.ManagedProviderConnectionHelper.Prepare()
   in NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.GetReservedWords(Dialect dialect, IConnectionHelper connectionHelper)
   in NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.GetReservedWords(IDictionary`2 cfgProperties)
   in NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.QuoteTableAndColumns(Configuration configuration)
   in HbmSchemaExporter.NHibernateManager.BuildNHConfig(String connectionString, DbType dbType, Dialect& requiredDialect)
   in HbmSchemaExporter.NHibernateManager.GenerateSql(MainWindowViewModel viewModel)
   in HbmSchemaExporter.MainWindow.btnGenerateSql_Click(Object sender, RoutedEventArgs e)

The question is obvious: what's wrong in code or configuration? I am sure that password is typed correctly, I have copied the rest of the connection string and replaced password placeholder. I have set up firewall rules and AFAIK without firewall rules you can't use https://yourdbhost.database.windows.net/

Answer 1

I've been searching for an answer to this exact question for a couple of hours and the question comments above led me to remove the square brackets from the connection string provided by the Azure Management portal.

The connection was successful without the square brackets. My connection string is now (replace bold sections with real details):

Server=tcp:SERVER.database.windows.net,1433;Database=MYDB;User ID=USER@SERVER;Password=PASSWORD;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;

Answer 2

Believe it or not, this connection string worked together with slight modifications to FNH code (only after changing the conn string it started to work)

Data Source=xxxxx.database.windows.net;Initial Catalog=MyDb;Persist Security Info=True;User ID=zshop;Password=xxxxxxx

Instead of using Database I had to use Initial Catalog and Persist Security Info. Without brackets for DB name

Since the error reported login failure I believe the culprit was Persist Security Info.

Now I have another problem to be discussed in another question