0

The export of the Azure database to storage - OK. <db-name>-<date-time>.bacpac created successfully.

The import from that .bacpac to a local SQL Server - OK; the sa user can login and use the database.

The local server has been configured with

sp_configure 'contained database authentication', 1;

and I can create new users

create user [test_new] 
    with password = 'some&very=secret-password!'

and this new user can successfully log in.

But the users contained in the database before the export can not login with their passwords.

I can alter and make them new password and that works.

alter user [existed_user] 
    with password = 'some new or even the same pwd'

My understanding that contained database specifically designed to carry auth information with it. But for some reasons it does not work.

The server response error 18456, state 65 (that is user exists but password does not match).

Are there specific actions I have missed?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mx.Wolf
  • 578
  • 5
  • 13

1 Answers1

2

I tested also with scripting the user with the same result.

Passwords are not stored in SQL Server in plain text; they are hashed. And the hashing algorithm is undocumented and can vary among versions. But the hashing details for SQL Server aren't really secret, as you can attach a debugger or run PWDCOMPARE offline.

So it looks like the password hashing in Azure SQL Database is not the same as for "regular" SQL Server, which makes sense as it makes it more difficult to perform an offline attack against Azure SQL Database passwords.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67