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/

(might be a stupid comment) SQL Azure requires SSL. Have you checked that your connection string contains Encrypt=True; ? — jbl
– jbl, Commented Mar 22, 2013 at 10:45
+1 because it's not a stupid comment :), anyway Encrypt=True in my connection string! — usr-local-ΕΨΗΕΛΩΝ
– usr-local-ΕΨΗΕΛΩΝ, Commented Mar 22, 2013 at 11:03
It may help if you edit the original question to include the connection string (without real db credentials, of course...). — David Makogon
– David Makogon, Commented Mar 22, 2013 at 11:54
Have you tried without the @dbhost in the login ? I think I remember it was mandatory in the first versions of SQL Azure, but you should not include it anymore — jbl
– jbl, Commented Mar 22, 2013 at 13:10

SimonM · Accepted Answer · 2013-03-31 01:39:51Z

2

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;

answered Mar 31, 2013 at 1:39

SimonM

1743 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

usr-local-ΕΨΗΕΛΩΝ Over a year ago

I think I'll accept this one. Though changing the connection string format worked, I also removed brackets

usr-local-ΕΨΗΕΛΩΝ · Accepted Answer · 2013-03-23 13:23:12Z

0

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

answered Mar 23, 2013 at 13:23

usr-local-ΕΨΗΕΛΩΝ

27.1k36 gold badges173 silver badges330 bronze badges

Collectives™ on Stack Overflow

Cannot connect to SQL Azure from local machine

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related