Cannot login with new SQL User - SQL 2014

Question

I'm having some trouble with logging in with a newly created SQL User.

I have created a login and user on my SQL Server. I have changed the SQL Authentication mode to mixed and restarted the service.

Each time I try to login it gives me this error: Login failed for user 'BI_USER'. (Microsoft SQL Server, Error 18456)

Since this error description is pretty minimal I looked in the event viewer of my SQL Machine and this is what I find: Login failed for user 'BI_USER'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: x.x.x.x]

This really confused me...I double checked if this is the case but in SSMS I still see that my server is in Mixed mode.

I even checked with a T-SQL statement and this statement returns me that I'm in mixed mode:

DECLARE @AuthenticationMode INT

EXEC master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE'                            
    ,   N'Software\Microsoft\MSSQLServer\MSSQLServer'      
    ,   N'LoginMode' 
    ,   @AuthenticationMode OUTPUT  

SELECT 
    CASE @AuthenticationMode    
        WHEN 1 THEN 'Windows Authentication'   
        WHEN 2 THEN 'Windows and SQL Server Authentication'   
        ELSE 'Unknown'  
    END as [Authentication Mode]  

Anyone else has some expierence with this error and how to solve this?

KR,

Kevin

Answer 1

For testing purposes only does authentication pass for user BI_USER if the users SERVER ROLE is edited to give it the sysadmin privilege. If the answer is yes remove the sysadmin privilege and attempt to isolate which privilege is required to authenticate. What OS is SQL 2014 hosted on out of interest ?