Login failed for user - Microsoft SQL Server, Error: 4064

  • Johan Bijnens

    SSC Guru

    Points: 134254

    The goal was to figure out if a login is member of n-windows groups.

    e.g. this will show all auth paths for the given windows login (not a group)

    -- how does this login get to the database (authority paths)

    EXEC master..xp_logininfo @acctname = 'PBTF\bob',@option = 'all'

    go

    btw if you grant auth to a group, every member of that group has the same rights to do things, wright ?

    I would advise to split groups for your dba/sysadmins and the service accounts that you are using.

    SQLserver will set the needed auth for its service accounts during the set/alter operation of these service accounts.

    The goal is to have minimal authorized settings in use. (keep doors closed)

    My guess is you should at least revise these:

    PBTF\SvcSPContent -- my guess to execute some type of sprocs

    PBTF\SvcSPSearch -- my guess to execute search sprocs

    PBTF\SvcSPSQL -- please don't let this be to execute dynamic sql.

    PBTF\SvcSQL -- my guess your "regular" sqlserver service account (sqlserver will set the needed auth !)

    PBTF\SvcSQLRS -- my guess your regular Report Server service account (sqlserver will set the needed auth !)

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Perry Whittle

    SSC Guru

    Points: 233771

    paulc.byrum (3/29/2010)


    The description for Event ID ( 18456 ) in Source ( MSSQL$MICROSOFT##SSEE ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer.

    the instance mentioned here is the SQL Server 2005 embedded edition or Internal database instance. Do not mess with this as it can break things. Also service packs,etc may overwrite objects in here.

    the correct command to connect to this instance is

    sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query –E

    it only uses named pipes. Can you confirm the instance name of the Sharepoint instance?

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • ourehman

    SSC Veteran

    Points: 267

    Can you login with some other admin account.

  • Perry Whittle

    SSC Guru

    Points: 233771

    ourehman (3/30/2010)


    Can you login with some other admin account.

    see my post immediately above, he needs to confirm he is logging into the correct instance first!

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • paulc.byrum

    Say Hey Kid

    Points: 697

    I tried:

    sqlcmd -E -d master -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

    and then:

    ALTER LOGIN [PBTF\Group - IT DB Admins] WITH DEFAULT_DATABASE = master

    No errors running the command, but I still see Error 4064 in Management Studio.

    I also logged into Management Studio and tried:

    exec sp_defaultdb '[PBTF\Group - IT DB Admins]', 'master'

    Command completed successfully, but the Error 4064 still occurs.

    I will ask somebody with DBA rights on the SQL Server to login and add PBTF\bob.

  • Perry Whittle

    SSC Guru

    Points: 233771

    paulc.byrum (3/31/2010)


    I tried:

    sqlcmd -E -d master -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

    and then:

    ALTER LOGIN [PBTF\Group - IT DB Admins] WITH DEFAULT_DATABASE = master

    No errors running the command, but I still see Error 4064 in Management Studio.

    I also logged into Management Studio and tried:

    exec sp_defaultdb '[PBTF\Group - IT DB Admins]', 'master'

    Command completed successfully, but the Error 4064 still occurs.

    I will ask somebody with DBA rights on the SQL Server to login and add PBTF\bob.

    you're trying to add the windows login to the Sharepoint instance arent you?

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Perry Whittle

    SSC Guru

    Points: 233771

    paulc.byrum (3/26/2010)


    Perhaps, my problem is that I have several instanaces of SQL Server. They are:

    SQL Server 9.0.4035 (SharePoint instance)

    SQL Server 8.0.2055 (Standard edition)

    SQL Server 8.0.760 (MSDE)

    SQL Server 9.0.4053 (Express Edition)

    The instance with the problem is "SQL Server 9.0.4035 (SharePoint instance)".

    If I am using the sqlcmd, how do I ensure I am using the correct instance?

    Is the ALTER LOGIN command availble in this instance?

    the instance you refer to below is not the sharepoint instance

    \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

    it is the embedded instance used by WSUS, etc

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • paulc.byrum

    Say Hey Kid

    Points: 697

    Yes, that is right. A user in the security group is connected and receives the error.

  • shijobaby

    SSC Veteran

    Points: 203

    This error can be solved by changing default conect DB

    http://answerforu.com/2013/05/05/sql-server-error-4064-cannot-open-user-default-database-login-failed-login-failed-for-user-username/[/url]

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply