locked
sysadmin role for users in Azure SQl servers for managing backups RRS feed

  • Question

  • I am trying to connect to a SQL server raise in azure with RedGate SQL Backup with the user that is created when creating a new database

    But the next problem raise :

        Login Failure with nameserver.database.sindow...
        A problem was encounter whilst comunicating with the server
        The user credentials supplied do not represent a member of the sysadmin role, 
        SQL Backup requieres sysadmin privileges
     
        Do Admin users do not have sysadmin permisions?
     
    Do azure SQL servers do not have sysadmin roles?

    Things I have try :

    - http://msdn.microsoft.com/en-us/library/azure/dn133151.aspx

          By default, the user account specified during provisioning is a member of the local Administrators group.
          This administrator account is also the member of the SQL Server sysadmin server role.
          BUT this seems to not be the case, or it doesn't work

    http://egeek.dk/grant-ad-user-sysadmin-access-to-sql-server

        On the step Expand the Security folder, right click on “Logins” and chose “New Login”
        In my Microsoft SQL Server Management Studio, instead of a menu, it loads the next script :
        CREATE LOGIN <SQL_login_name, sysname, login_name>      
              WITH PASSWORD = '<password, sysname, Change_Password>'  GO
        to create the user, that's not usual...

    SQL executions :

        SELECT * FROM sys.sql_logins;
           name                                 AdminName
          principal_id                         1
          sid                                      0x0106...
          type                                    S
          type_desc                           SQL_Login
          is_disabled                          0
          create_date                        2014-07...
          modify_date                        2014-07...
          default_database_name     master
          default_language_name    us_english
          credential_id                      NULL
          is_policy_checked              0
         is_expiration_checked        0
         password_hash                 0x020...


     SELECT * FROM sys.server_role_members; 
           Error raise:
           Invalid object name 'sys.server_role_members'.
           Is that right ?
     
     EXEC [sys].[sp_helprole]
         RoleName
           public
           dbmanager
           loginmanager
           db_owner
           db_accessadmin
           db_securityadmin
          db_ddladmin
          db_backupoperator
          db_datareader
          db_datawriter
          db_denydatareader
          db_denydatawriter
      - No sysadmin again ?
     
     EXEC sp_addrolemember @rolename = 'sysadmin', @membername = 'PAPELclubSvAdmin01'
          Error again :
          Cannot alter the role 'sysadmin', because it does not exist or you do not have permission.



    Any help how could I enable sysadmin permision to the creator of the server and only existing user so far ?
    Thursday, July 31, 2014 11:01 AM

Answers

  • In response to Sofiya Li, I see that Azure offer two options for backups:

    - duplicate the database (what can be good and fast in case to revert changes, BUT it is not a real backup, even less for historical backup purpose)

    - export a bacpac (that it has its limitations, plenty of limitations, and therefore can not be considered a good Backup)

    According the security roles on SQL azure, I am still not sure that it is not possible to add sysadmin to a user, to be able to perform SQLbakups on azure,

    COULD YOU CONFIRM THAT AZURE SQL DBs DO NOT ALLOW admin ROLE, and therefore, NEITHER REAL BACKUPS?

    In response to Mekh, yes, I can connect to the server with no problem, but queries like :
    - EXEC sp_addsrvrolemember
    - EXEC sp_grantlogin

    return "Could not find stored procedure ..."

    so, it is a matter with the lack of (in server, or granted) the sysadmin role, I think

    Probably I got used to work with local (own servers) SQL implementations, where we could have real access, peacefulness in backup times are important to plenty of us


    Hi ppGtnd,

    When you generate a SQL Azure server via the SQL Azure portal, you generate a user name and password at the same time. This is your administrative account it has access to all databases on that server. And you can execute backup the SQL Azure database via this administrative account. There is an article about adding a user to SQL Azure database.

    http://blogs.msdn.com/b/sqlazure/archive/2010/06/21/10028038.aspx

    As the above post, if you want to grant the permission to a login, you can only use the stored produce of sp_addrolemember.  In SQL Azure database, it does not support the stored produce of sp_addsrvrolemember, sp_grantlogin. If you have Web or Business Edition databases, you can use database copy or Import and Export services to create copies of the data and export the file to an Microsoft Azure storage account.

    When you copy a database to a different SQL Database server, the login must have db_owner permissions on the source server and dbmanager on the destination server.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Proposed as answer by Mekh Subba Thursday, October 16, 2014 9:35 AM
    • Marked as answer by Mekh Subba Thursday, October 30, 2014 7:08 AM
    Tuesday, August 19, 2014 3:25 AM

All replies

  • Hi,

    Were you ever able to connect to the server ?

    Have you checked the ports and the endpoints?

    Please check and let me know.

    Regards,

    Mekh.

    Thursday, July 31, 2014 6:27 PM
  • Hi ppGrnd,

    According to your description, in Azure platform, if you want to create user in SQL Azure database, the SQL Azure portal does not allow you to administrate additional users and logins via GUI, in order to do this, you need to use Transact-SQL in SQL Azure portal or the SQL Server Management Studio.

    Usually, you must be connected to the master database on SQL Azure with the administrative login (which you get from the SQL Azure portal) to execute the CREATE LOGIN command, the CREATE User command, and the GRANT User Permissions command .

    In addition, the security permissions in Microsoft Azure SQL Database are different from the security administration for an on-premises instance of SQL Server . In SQL Server instance, there are fixed server and fixed database roles, but in Microsoft  Azure SQL Database , one Azure SQL Database server-level principal account always has permission to manage all server-level and database-level security. In order for logins other than the server-level principal to manage server-level security, Microsoft Azure SQL Database only offers two security roles: loginmanager and dbmanager. For more information, see: http://msdn.microsoft.com/en-us/library/ee336235.aspx

    In Azure platform, you can only check one row for each member of each database role via sys.database_role_members.

    For more information, see: http://msdn.microsoft.com/en-us/library/ms189780.aspx

    There is an article about Windows Azure SQL database Backup and Restore strategy, you can review the following link.

    http://social.technet.microsoft.com/wiki/contents/articles/1792.windows-azure-sql-database-backup-and-restore-strategy.aspx

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Friday, August 8, 2014 1:27 AM
    • Unmarked as answer by ppGrnd Tuesday, August 12, 2014 9:16 AM
    Friday, August 1, 2014 2:10 AM
  • In response to Sofiya Li, I see that Azure offer two options for backups:

    - duplicate the database (what can be good and fast in case to revert changes, BUT it is not a real backup, even less for historical backup purpose)

    - export a bacpac (that it has its limitations, plenty of limitations, and therefore can not be considered a good Backup)

    According the security roles on SQL azure, I am still not sure that it is not possible to add sysadmin to a user, to be able to perform SQLbakups on azure,

    COULD YOU CONFIRM THAT AZURE SQL DBs DO NOT ALLOW admin ROLE, and therefore, NEITHER REAL BACKUPS?

    In response to Mekh, yes, I can connect to the server with no problem, but queries like :
    - EXEC sp_addsrvrolemember
    - EXEC sp_grantlogin

    return "Could not find stored procedure ..."

    so, it is a matter with the lack of (in server, or granted) the sysadmin role, I think

    Probably I got used to work with local (own servers) SQL implementations, where we could have real access, peacefulness in backup times are important to plenty of us

    • Edited by ppGrnd Tuesday, August 12, 2014 9:16 AM add info
    Monday, August 11, 2014 10:22 AM
  • In response to Sofiya Li, I see that Azure offer two options for backups:

    - duplicate the database (what can be good and fast in case to revert changes, BUT it is not a real backup, even less for historical backup purpose)

    - export a bacpac (that it has its limitations, plenty of limitations, and therefore can not be considered a good Backup)

    According the security roles on SQL azure, I am still not sure that it is not possible to add sysadmin to a user, to be able to perform SQLbakups on azure,

    COULD YOU CONFIRM THAT AZURE SQL DBs DO NOT ALLOW admin ROLE, and therefore, NEITHER REAL BACKUPS?

    In response to Mekh, yes, I can connect to the server with no problem, but queries like :
    - EXEC sp_addsrvrolemember
    - EXEC sp_grantlogin

    return "Could not find stored procedure ..."

    so, it is a matter with the lack of (in server, or granted) the sysadmin role, I think

    Probably I got used to work with local (own servers) SQL implementations, where we could have real access, peacefulness in backup times are important to plenty of us


    Hi ppGtnd,

    When you generate a SQL Azure server via the SQL Azure portal, you generate a user name and password at the same time. This is your administrative account it has access to all databases on that server. And you can execute backup the SQL Azure database via this administrative account. There is an article about adding a user to SQL Azure database.

    http://blogs.msdn.com/b/sqlazure/archive/2010/06/21/10028038.aspx

    As the above post, if you want to grant the permission to a login, you can only use the stored produce of sp_addrolemember.  In SQL Azure database, it does not support the stored produce of sp_addsrvrolemember, sp_grantlogin. If you have Web or Business Edition databases, you can use database copy or Import and Export services to create copies of the data and export the file to an Microsoft Azure storage account.

    When you copy a database to a different SQL Database server, the login must have db_owner permissions on the source server and dbmanager on the destination server.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Proposed as answer by Mekh Subba Thursday, October 16, 2014 9:35 AM
    • Marked as answer by Mekh Subba Thursday, October 30, 2014 7:08 AM
    Tuesday, August 19, 2014 3:25 AM
  • Hi,

    I'm marking Sofiya's post as answer since this post has been idle for sometime now. Please Click “Unmark as Answer” if a marked post does not actually answer your question.Please feel free to post any issues which you may have. We would be glad to assist further.

    Regards,

    Mekh.

    Thursday, October 30, 2014 7:08 AM