none
New user is not able to access the database RRS feed

  • Question

  • Hello everyone,

    I'm having trouble with new user.

    -- connect to master
    CREATE LOGIN MY_TEST_USER WITH PASSWORD = '<pass>'
    -- connect to my-db as my default user
    CREATE USER MY_TEST_USER FOR LOGIN MY_TEST_USER
    CREATE ROLE MY_TEST_ROLE
    EXEC sp_addrolemember 'MY_TEST_ROLE', 'MY_TEST_USER'
    
    -- connect to master
    CREATE LOGIN MY_TEST_SCOPED_USER WITH PASSWORD = '<pass>'
    -- connect to my-db as my default user
    CREATE SCHEMA "MY_TEST_SCOPED_SCHEMA"
    CREATE USER MY_TEST_SCOPED_USER FOR LOGIN MY_TEST_SCOPED_USER WITH DEFAULT_SCHEMA = MY_TEST_SCOPED_SCHEMA
    CREATE ROLE "MY_TEST_SCOPED_ROLE"
    GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON SCHEMA::MY_TEST_SCOPED_SCHEMA TO "MY_TEST_SCOPED_ROLE"
    EXEC sp_addrolemember 'MY_TEST_SCOPED_ROLE', 'MY_TEST_SCOPED_USER'
    GRANT CREATE TABLE TO "MY_TEST_SCOPED_ROLE"
    -- connect as MY_TEST_USER or MY_TEST_SCOPED_USER will fail with
    -- The server principal "MY_TEST_SCOPED_USER" is not able to access the database "my-db" under the current security context.
    

    I want to set up permissions like, this.

    I have user and login for MY_TEST_USER with MY_TEST_ROLE which has permissions above other users with scoped privileges only for single schema.

    I did created new user and login MY_TEST_SCOPED_USER which has access to MY_TEST_SCOPED_SCHEMA.

    Now when I try to connect as this user I get error: The server principal "MY_TEST_SCOPED_USER" is not able to access the database "my-db" under the current security context.
    I can't figure out what is wrong and why I can't login.

    I'm using odbc driver with php and close connection before new connect.


    Monday, January 13, 2020 3:13 PM

All replies

  • Hi Martin,

    What is the default database value being passed in the connection string. With SSMS, there are additional options to be able to select a specific database or use the value of <default>. I want to verify if you are passing a value in the connection string to specifically connect to the a database where this login has been granted access for?

    Thank you,

    Mike

    Tuesday, January 14, 2020 6:11 PM
    Moderator
  • Hi Mike,

    thanks for answer, I'm testing on `Gen2: DW100c` there is only one database per DWU so I'm using always same database which is set in connection string.

    Martin

    Tuesday, January 14, 2020 7:13 PM
  • Martin,

    I ran through both your scenarios and using SSMS, I am able to connect with Login/User created in each scenario:

    In master:

    CREATE LOGIN testuser WITH PASSWORD = 'P8ssW0Rd!';
    CREATE LOGIN testuser2 WITH PASSWORD = 'P8ssW0Rd!';

    In AdventureWorksDW:

    -- connect to my-db as my default user
    CREATE USER Test_User FOR LOGIN testuser
    CREATE ROLE TEST_ROLE
    EXEC sp_addrolemember 'TEST_ROLE', 'Test_User'
    
    -- connect to my-db as my default user
    CREATE SCHEMA "MY_TEST_SCOPED_SCHEMA";
    CREATE USER TEST_SCOPED_USER FOR LOGIN testuser2 WITH DEFAULT_SCHEMA = MY_TEST_SCOPED_SCHEMA;
    CREATE ROLE "MY_TEST_SCOPED_ROLE";
    GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE ON SCHEMA::MY_TEST_SCOPED_SCHEMA TO "MY_TEST_SCOPED_ROLE"
    EXEC sp_addrolemember 'MY_TEST_SCOPED_ROLE', 'TEST_SCOPED_USER';
    GRANT CREATE TABLE TO "MY_TEST_SCOPED_ROLE"

    I had to explicitly declare the specific database I wanted to connect with for this to work, as I received the same error as you were seeing. Please make sure your error message didn't say master.

    And I am able to connect with both testuser and testuser2:

    Please let me know if you found any differences from my steps from yours.

    Thank you,

    Mike


    Tuesday, January 14, 2020 10:28 PM
    Moderator
  • Martin would you be able to share the connection string which you are using in order to connect to the DB as well as the version of ODBC which you are using. It would appear that you are not specifying the credential type or incorrect string. please share without username and password 

    Tuesday, January 14, 2020 11:13 PM
  • Hi,

    I made minimal example with dump of `sqlsrv_connect` arguments.

    -- Connect SRV:"tcp:<server>.database.windows.net,1433", INFO: "UID=><UID>;pwd=><PWD>;Database=>master;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    CREATE LOGIN MY_USER WITH PASSWORD = 'strong4PassWord1'
    -- Connect SRV:"tcp:<server>.database.windows.net,1433", INFO: "UID=><UID>;pwd=><PWD>;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    CREATE USER MY_USER FOR LOGIN MY_USER
    CREATE ROLE MY_ROLE
    EXEC sp_addrolemember 'MY_ROLE', 'MY_USER'
    -- Connect SRV:"tcp:<server>.database.windows.net,1433", INFO: "UID=>MY_USER;pwd=>strong4PassWord1;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    -- [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The server principal "MY_USER" is not able to access the database "test-synapse" under the current security context.
    --    Cannot open database "test-synapse" requested by the login. The login failed.
    It's kind of super weird, I try to connect to database with PHPStorm(JDBC) and I'm connected. I would suspect some timeout, since this commands are executed immediately one after another. But I'm calling `sqlsrv_connect` in loop when it fails with increasing timeout up to 10s.

    Wednesday, January 15, 2020 10:41 AM
  • Hi Martin,

    In my case with the SSMS example, there is at least a 30 second delay between the time I pass credentials for authorization and when the Object Explorer populates with the SQL Server instance and all the security elements applied. 

    Can you increase the timeout value to ensure the authorization is successful by setting to 30s as a test.

    Thanks,

    Mike 

    Wednesday, January 15, 2020 5:13 PM
    Moderator
  • Hi Mike,

    I set up decent increasing timeout and made minimal example using docker for you to try,

    https://github.com/zajca/synapse-err-mininal-example


    I also find that login is unstable:

    Here is output of script:

    09:05:00 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=><uid>;pwd=><pwd>;Database=>master;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:05:00 RUN: CREATE LOGIN MY_USER WITH PASSWORD = 'strong4PassWord1'
    09:05:00 Success
    09:05:00 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=><uid>;pwd=><pwd>;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:05:01 RUN: CREATE USER MY_USER FOR LOGIN MY_USER
    09:05:01 Success
    09:05:01 RUN: CREATE ROLE MY_ROLE
    09:05:01 Success
    09:05:01 RUN: EXEC sp_addrolemember 'MY_ROLE', 'MY_USER'
    09:05:01 Success
    09:05:06 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=>MY_USER;pwd=>strong4PassWord1;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:05:16 Initializing Synapse connection failed: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The server principal "MY_USER" is not able to access the database "test-synapse" under the current security context.
    Cannot open database "test-synapse" requested by the login. The login failed.
    Login failed for user 'MY_USER'.
    09:05:18 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=>MY_USER;pwd=>strong4PassWord1;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:05:29 Initializing Synapse connection failed: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The server principal "MY_USER" is not able to access the database "test-synapse" under the current security context.
    Cannot open database "test-synapse" requested by the login. The login failed.
    Login failed for user 'MY_USER'.
    09:05:33 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=>MY_USER;pwd=>strong4PassWord1;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:05:43 Initializing Synapse connection failed: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The server principal "MY_USER" is not able to access the database "test-synapse" under the current security context.
    Cannot open database "test-synapse" requested by the login. The login failed.
    Login failed for user 'MY_USER'.
    09:05:51 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=>MY_USER;pwd=>strong4PassWord1;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:06:01 Initializing Synapse connection failed: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The server principal "MY_USER" is not able to access the database "test-synapse" under the current security context.
    Cannot open database "test-synapse" requested by the login. The login failed.
    Login failed for user 'MY_USER'.
    09:06:17 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=>MY_USER;pwd=>strong4PassWord1;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:06:27 Initializing Synapse connection failed: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The server principal "MY_USER" is not able to access the database "test-synapse" under the current security context.
    Cannot open database "test-synapse" requested by the login. The login failed.
    Login failed for user 'MY_USER'.
    09:06:59 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=>MY_USER;pwd=>strong4PassWord1;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:07:10 Initializing Synapse connection failed: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The server principal "MY_USER" is not able to access the database "test-synapse" under the current security context.
    Cannot open database "test-synapse" requested by the login. The login failed.
    Login failed for user 'MY_USER'.
    09:07:10 RUN: CREATE TABLE "test" (
        "id" varchar(8000) NOT NULL,
        "name" varchar(8000) NOT NULL,
        "_timestamp" datetimeoffset,
        PRIMARY KEY NONCLUSTERED("id") NOT ENFORCED
    )
    09:07:10 Error: sqlsrv_query() expects parameter 1 to be resource, bool given
    09:07:10 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=><uid>;pwd=><pwd>;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:07:10 RUN: EXEC sp_droprolemember 'MY_ROLE', 'MY_USER'
    09:07:10 Success
    09:07:10 RUN: DROP USER "MY_USER"
    09:07:10 Success
    09:07:10 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=><uid>;pwd=><pwd>;Database=>master;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:07:11 RUN: DROP LOGIN "MY_USER"
    09:07:11 Success
    09:07:11 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=><uid>;pwd=><pwd>;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:07:11 RUN: DROP ROLE "MY_ROLE"
    09:07:11 Success

    But I run same script different time with output:

    09:04:09 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=><uid>;pwd=><pwd>;Database=>master;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:04:09 RUN: CREATE LOGIN MY_USER WITH PASSWORD = 'strong4PassWord1'
    09:04:09 Success
    09:04:09 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=><uid>;pwd=><pwd>;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:04:10 RUN: CREATE USER MY_USER FOR LOGIN MY_USER
    09:04:10 Success
    09:04:10 RUN: CREATE ROLE MY_ROLE
    09:04:10 Success
    09:04:10 RUN: EXEC sp_addrolemember 'MY_ROLE', 'MY_USER'
    09:04:10 Success
    09:04:15 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=>MY_USER;pwd=>strong4PassWord1;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:04:15 RUN: CREATE TABLE "test" (
        "id" varchar(8000) NOT NULL,
        "name" varchar(8000) NOT NULL,
        "_timestamp" datetimeoffset,
        PRIMARY KEY NONCLUSTERED("id") NOT ENFORCED
    )
    09:04:16 Error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]User does not have permission to perform this action.
    Changed database context to 'test-synapse'.
    09:04:16 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=><uid>;pwd=><pwd>;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:04:16 RUN: EXEC sp_droprolemember 'MY_ROLE', 'MY_USER'
    09:04:16 Success
    09:04:16 RUN: DROP USER "MY_USER"
    09:04:16 Success
    09:04:16 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=><uid>;pwd=><pwd>;Database=>master;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:04:16 RUN: DROP LOGIN "MY_USER"
    09:04:16 Success
    09:04:16 Connect SRV:"tcp:<srv>.database.windows.net,1433", INFO: "UID=><uid>;pwd=><pwd>;Database=>test-synapse;LoginTimeout=>30;Encrypt=>1;TrustServerCertificate=>0"
    09:04:17 RUN: DROP ROLE "MY_ROLE"
    09:04:17 Success
    

    Which is super weird. One time it's working and one not.

    Thursday, January 16, 2020 8:18 AM
  • I think that this is some sort of delay between droping login. But it's weird that CREATE LOGIN does not fail.
    Thursday, January 16, 2020 9:23 AM