Как создавать имена входа и пользователей в гео-избыточной базе данных в Azure?

Question

Мне нужно создать логин и пользователя в гео-реплицированной базе данных. Пользователю будет предоставлена ​​роль db_datareader в этой базе данных.

Попытка подхода состояла в том, чтобы использовать параметр SID (тот же идентификатор), как показано ниже:

-- 1. PRIMARY_DATABASE: Run on master database of Primary Database to create login
CREATE LOGIN [LOGINUSER] WITH password='XYZ'
GO

-- 2. Select details for created SQL login
SELECT [name], [sid] 
FROM [sys].[sql_logins] 
WHERE [type_desc] = 'SQL_Login'
AND [name] = 'LOGINUSER'
GO

-- 3. SECONDARY_DATABASE: Run on master database of Secondary Database to create login with associated SID
CREATE LOGIN [LOGINUSER]
WITH PASSWORD = 'XYZ',
SID = <SID from Step 2>
GO

В то время как создание логина отлично работает на обеих базах данных, мне был разрешен доступ на PRIMARY_DATABASE, и я отказал в доступе на SECONDARY_DATABASE со следующей ошибкой:

The server principal "LOGINUSER" is not able to access the database "master" under the current security context.
Cannot open user default database. Login failed.
Login failed for user 'LOGINUSER'. (Microsoft SQL Server, Error: 916)

1. Это правильный подход для настройки входа в гео-реплицированные базы данных?
2. Как настроить пользователя в этих базах?

Answer 1

Я столкнулся с той же проблемой, и у меня сработало следующее. У меня есть Azure SQL Server, на котором включена активная георепликация.

1. Убедитесь, что ваш IP-адрес добавлен в брандмауэр существующего и нового Сервера.

На база данных Первичного сервера:

2. Создать логин

      CREATE LOGIN geo_user WITH PASSWORD = 'secure-password'

3. Создайте соответствующего пользователя и назначьте ему роль dbmanager (вы можете назначить соответствующую роль в соответствии с вашими требованиями)

4. Обратите внимание на SID нового входа в систему:

      SELECT sid FROM sys.sql_logins WHERE name = 'geo_user'

На source база данных Первичного сервера:

5. Создайте пользователя с таким же логином:

      CREATE USER geo_user FOR LOGIN geo_user

6. Добавьте пользователя в роль db_owner:

      ALTER ROLE db_owner add MEMBER geo_user

На master Вторичного сервера:

7. Создайте тот же логин, что и на основном сервере (используя те же имя пользователя, пароль и SID из шага 4):

      CREATE LOGIN geo_user with password = 'secure-password', sid=0x01060000000000640000000000000000A0D03563024DA846ADBCF33D31B6C026

8. Создайте соответствующего пользователя и назначьте ему роль dbmanager:

      CREATE USER geo_user FOR LOGIN geo_user

ALTER ROLE dbmanager ADD MEMBER geo_user 

Теперь вы должны иметь возможность войти на новый сервер с помощью этого пользователя и запросить базу данных.

Answer 2

Вы должны также создать логины на другом сервере из SID, взятого с первичного сервера. В противном случае вы можете использовать функцию автономных пользователей в SQL server / db, чтобы пользователи автоматически реплицировались в копию только для чтения.

Answer 3

Похоже, у вас нет локального доступа к master db. Вы пытались получить доступ к мастеру через другой логин? Если нет, вы можете создать локального пользователя в master и подключиться с его помощью.

Answer 4

Вы должны использовать contained usersвместо. Эти пользователи содержатся в базе данных, синхронизируются с георепликациями и не нуждаются в дополнительном входе в систему изmaster Таблица.

Создайте автономного пользователя с паролем как db_owner:

USE mydatabase;
CREATE USER myuser WITH PASSWORD = 'secret';
EXEC sp_addrolemember 'db_owner', 'myuser'

дополнительная информация: https://docs.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-ver15