none
Problems with "Create User With Password"

    Question

  • Hi there,

    in my Situation "UserOne" is a contained database user (without sqllogin) and the user is dbo in the contained database.
    With this user i create other users with password in the contained database.
    The CREATE USER WITH PASSWORD Statement executed with the Entity Framework works and the new user is in the database.

    But after that, the next SQL-Access (e.g. SELECT * FROM Products) of "UserOne" through the Entity Framework crashes with the Error.
    2012-11-22 11:29:10.17 Logon       Login failed for user 'S-1-9-3-3794712727-1332191744-3365646766-2628027274.'. Reason: Could not find a login matching the name provided. [CLIENT: 127.0.0.1]
    2012-11-22 11:29:10.17 spid55      Error: 18056, Severity: 20, State: 5.
    2012-11-22 11:29:10.17 spid55      The client was unable to reuse a session with SPID 55, which had been reset for connection pooling. The failure ID is 5. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

    Whats the problem here? Do the user who whats to create other contained database users need a SQLServer Login?
    Because everything works fine, when "UserOne" has a SQLLogin!?

    But I thought that the sense of contained database users is that they don't need a SQLServer Login?

    Hope anyone can help me?

    Greetings
    Martin



    • Edited by FuE.Martin Thursday, November 22, 2012 11:08 AM
    Thursday, November 22, 2012 11:06 AM

All replies

  • Hello Martin,

    After creating the contained user, you need to assign him necessary permissions to access the database. In this case, you may need to grant Select permission on the selective table, or db_datareader permission, which assigns read permissions on all tables in the database.

    USE [demodb];
    go
    alter role db_datareader add member [userone];
    go

    You may also need to consider that, you must explicitly specify the database name for a contained user, otherwise the user may not be able to login to database, as the default authentication of a Login is on master database.

    Hope, this may help :)


    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

    Thursday, November 22, 2012 11:44 AM
  • Hello Sudeepta,

    thanks for your answer, but I think I do all of that and it doesnt work. Meanwhile I use a stored procedure for creating the contained database users.
    Maybe the problem relates to the Entity Framework Connection Pooling?
    There is no problem with any SQL statement my application fires until I come to the point of creating a new application user which is a new "user with password" in our contained database. The stored procedure executes without any error but the next SQL statement crashes.

    This is my stored procedure:

    USE [ixxais]
    GO
    /****** Object:  StoredProcedure [dbo].[AddUser]    Script Date: 22.11.2012 13:02:24 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[AddUser]
     @benutzer AS SYSNAME,
     @passwort AS SYSNAME
    AS
    BEGIN
    
     DECLARE @myError int
    
     EXEC('CREATE USER [' + @benutzer + '] WITH PASSWORD = ''' + @passwort + ''', DEFAULT_SCHEMA= [dbo], DEFAULT_LANGUAGE=[NONE];
           GRANT CONNECT TO [' + @benutzer + '];
        ALTER ROLE [db_owner] ADD MEMBER [' + @benutzer + '];
        GRANT ALTER ANY USER TO [' + @benutzer + ']')
    
     SELECT @myError = @@Error
    
     RETURN @myError
    
    END
    

    Any other ideas?

    Thursday, November 22, 2012 12:14 PM
  • Hello Martin,

    Did you try to execute the SP to create the contained user from Management Studio? If you are able to do that, then I think, it may be an issue with your front-end app (sorry, I may not be helpful in that).

    What error messages are logged in the SQL Server error log?


    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

    Thursday, November 22, 2012 12:39 PM
  • Hi again,

    in the SQL Server error log I found:

    2012-11-22 11:29:10.17 Logon       Login failed for user 'S-1-9-3-3794712727-1332191744-3365646766-2628027274.'. Reason: Could not find a login matching the name provided. [CLIENT: 127.0.0.1]
    2012-11-22 11:29:10.17 spid55      Error: 18056, Severity: 20, State: 5.
    2012-11-22 11:29:10.17 spid55      The client was unable to reuse a session with SPID 55, which had been reset for connection pooling. The failure ID is 5. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

    Yes, my stored procedure works in SSMS - it works in my front end app too, but the next sql statement after the execution of the stored procedure failed, with the error you can read above (Login failed for user ...). Seems that the connection looses it's credentials or the fact that it is a contained database and therefore no need for a SQL Server login!?


    • Edited by FuE.Martin Thursday, November 22, 2012 7:35 PM
    Thursday, November 22, 2012 7:29 PM
  • Hello Martin,

    As I mentioned earlier, the contained user is trying to authenticate aganist the master database, that's why you got the above error.

    You have to provide the Contained database name along with the contained user name and password.


    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

    Friday, November 23, 2012 10:11 AM
  • Hello Sudeepta,

    I can replay the error in a small c# codesnippet. The first interation works fine, the second interation crashes on executing the stored procedure with the known "Login failed" error. As you can see, in every interation a set the connection string and I create a new SqlConnection object with the connection string.

    Do you have another advice?

    Snippet:

    string connString;
                int lfdNr = 1;
    
                for (int i = 1; i <= 3; i++)
                { 
                    connString ="data source=127.0.0.1;initial catalog=ixxais;User Id=Admin;Password=medatixx$2014;MultipleActiveResultSets=True;App=EntityFramework";
                    using (var PubsConn = new SqlConnection(connString))
                    {
                        SqlCommand testCMD = new SqlCommand("AddUser", PubsConn);
                        testCMD.CommandType = CommandType.StoredProcedure;
                        SqlParameter benutzer = testCMD.Parameters.Add("@benutzer", SqlDbType.VarChar, 128);
                        benutzer.Direction = ParameterDirection.Input;
                        SqlParameter passwort = testCMD.Parameters.Add("@passwort", SqlDbType.VarChar, 128);
                        passwort.Direction = ParameterDirection.Input;
    
                        benutzer.Value = "TestUser" + lfdNr.ToString();
                        lfdNr++;
                        passwort.Value = "abcdefgh$2014";
                        PubsConn.Open();
                        var teststring = PubsConn.ConnectionString;
                        var result = testCMD.ExecuteNonQuery();
    
                        testCMD = new SqlCommand("SELECT COUNT(*) FROM Benutzer", PubsConn);
                        testCMD.CommandType = CommandType.Text;
    
                        var count = testCMD.ExecuteScalar();
    
                        PubsConn.Close();
                    }
                }

    Friday, November 23, 2012 12:24 PM
  • I was able to reproduce the problem and I've reported it on
    https://connect.microsoft.com/SQLServer/feedback/details/772069/login-failure-with-connection-pooling-in-contained-db-after-connection-has-added-contained-user

    It only seems to happen when you add (contained) users to the database. With a plain command, things works as advertised.

    There is a simple workaround: add ;Pooling=false to the connection string. If my theory is correct, you only need this for this particular stored procedure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, November 23, 2012 11:07 PM
  • Hi Erland,

    thank your for testing my problem and for posting it on connect.microsoft.com.

    Saturday, November 24, 2012 6:42 PM