通过

SQL 2016 -- Why can’t I STRETCH my database (I have the right user name and password)?

  • 项目

Recently we got a call from customer who was trying to enable stretch database but repeatedly got errors like below:

Oct 14 2016 13:52:05 [Informational] TaskUpdates: Message:Task : 'Configure Stretch on the Database stretchdb6' -- Status : 'Running' -- Details : 'Task failed due to following error: Microsoft.SqlServer.Management.Smo.FailedOperationException: Alter failed for Database 'stretchdb6'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Login failed for user 'sqladmin'.
The server <server name>.database.windows.net' is not accessible. Ensure that the remote server exists and the Azure SQL DB Firewall Rules permit access to the server. If you believe that your server should be accessible please retry the command.
ALTER DATABASE statement failed.
   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext, Boolean executeForAlter)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplWorker()
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
   at Microsoft.SqlServer.Management.StretchDatabase.Model.Tasks.StretchDatabaseTask.Perform(IExecutionPolicy taskExecutionPolicy)
   at Microsoft.SqlServer.Management.StretchDatabase.Model.Common.Task.Perform(IExecutionPolicy policy, CancellationToken token, ScenarioTaskHandler taskDelegate), retrying ...'.

 

The message provided enough directions.  It says either you have a bad login or firewall setting on the Azure DB Server side is not configured correctly.     The very first thing is to ensure the Firewall was configured correctly.   We even tried 0.0.0.0. to 255.255.255.255. But it didn’t resolve the issue.

Next we created a brand new database on the same server and tried on that one.  It worked.  But customer just couldn’t get the old database to work even she made sure that she could use the login/password to log in using SSM on the same server to the Azure DB server. 

On the same server, brand new database worked but the old database didn’t.   So that made me wonder what happens if I manually cause an failure and later retry.   Here were the sequent of events I did that duplicate the issue.

  1. Try to enable but put a wrong password or user name
  2. It will fail
  3. Run this query to see the credential created: select * from sys.database_credentials
  4. Try to enable again, but put correct password/user name
  5. It will still fail.

 

It turned out that current configuration wizard doesn’t drop the old database credential that had bad password and keep using the old database credential.

Solution

Manually drop the database credential created earlier and reconfigure stretch and supply new correct login/password.

  • Get your credential name by using “select * from sys.database_credentials” (note that you need to use the database in question)

image

  • Then issue “DROP database SCOPED  CREDENTIAL[ <credential name from above>].  Note that  the credential name is not the login name.  It is the database credential name with .database.windows.net in it.  You also need to put [] around the drop datatement

 

 

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

Comments

  • Anonymous
    October 18, 2016
    If the database name is supposed to be invisible, it's not really working. Blank things with solid bars, not with irregular brush swipes.