Creating a login - Error: 18456, Severity: 14, State: 12

This used to be quite easy in SQL Server 2008, but now, on SQL Server 2012, it's not clear what the problem is.

I simply want to create a regular account that uses SQL Server authentication...

C:\>sqlcmd -Usa  -Pfoobar -S tcp:scott-mcintosh2,65352 -dmaster
1> select @@version
2> go

------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2012 - 11.0.2218.0 (X64)
        Jun 12 2012 13:05:25
        Copyright (c) Microsoft Corporation
        Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


(1 rows affected)
1> EXEC sp_addlogin 'scott2', 'Bailout6'
2> go
1> quit

C:\>sqlcmd -Uscott2  -PBailout6 -S tcp:scott-mcintosh2,65352
Msg 18456, Level 14, State 1, Server SCOTT-MCINTOSH2\SCOTTMCINTOSH, Line 1
Login failed for user 'scott2'.

This is the excerpt from the SQL Server error log:

2013-09-09 15:54:29.10 Logon       Error: 18456, Severity: 14, State: 12.
2013-09-09 15:54:29.10 Logon       Login failed for user 'scott2'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 127.0.0.1]

This blog entry claims that "State 12" involves underlying Windows permissions:
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

But I don't understand why that should be true. I'm creating a plain-text login account using SQL Server authentication (or so I thought). Or maybe the blog entry is mistaken about this.

Scott.
JonahGroupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

Have you enabled SQL Authentication?

Regards
  David
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
sp_addlogin is only there fore backwards compability.

Not sure it works on SQL 2012 DevEd 64 bit.
Why not try the supported syntax:
CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';
GO

See: http://technet.microsoft.com/en-us/library/ms189751.aspx

Regards Marten
0
JonahGroupAuthor Commented:
Thanks for the responses. Yes, SQL Server Authentication is turned on (see attachment).

"CREATE LOGIN" seems to have the same results as "sp_addlogin" in this case....

C:\>sqlcmd -Usa  -Pfoobar -S tcp:scott-mcintosh2,65352 -dmaster
1> CREATE LOGIN scott3  WITH PASSWORD='Bailout6'
2> go
1> quit

C:\>sqlcmd -Uscott3  -PBailout6 -S tcp:scott-mcintosh2,65352
Msg 18456, Level 14, State 1, Server SCOTT-MCINTOSH2\SCOTTMCINTOSH, Line 1
Login failed for user 'scott3'.

C:\>
Same message from the error log:

2013-09-10 14:31:56.08 Logon       Error: 18456, Severity: 14, State: 12.
2013-09-10 14:31:56.08 Logon       Login failed for user 'scott3'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 127.0.0.1]
9-10-2013-2-33-48-PM.jpg
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Haven't seen this one Before, but in the article I refere to below, it says that Error: 18456, Severity: 14, State: 12 means that the authentication is successful, but the server access fails due to permission. I e connect to server is not granted or something similar

You do not have a logon trigger on the server bye chance?

You could try and grant the connect rights to the account as described in the article:
GRANT CONNECT SQL TO "scott3"

You might try to write all the commands using the keyboard just in case some invisible char messes it up for you as a second alternative.

Link to the article:https://confluence.atlassian.com/display/CONFKB/Unable+to+Connect+to+SQL+Server+Due+to+Error%3A+18456,+Severity%3A+14,+State%3A+12


Regards Marten
0
JonahGroupAuthor Commented:
There is no logon trigger installed. I typed it all manually this time. Still no luck.

C:\>sqlcmd -Usa  -Pfoobar -S tcp:scott-mcintosh2,65352 -dmaster
1> CREATE LOGIN scott4 WITH PASSWORD='Bailout6'
2> go
1> GRANT CONNECT SQL TO scott4
2> go
1> GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO scott4
2> go
1> GRANT CONNECT ON ENDPOINT::[TSQL Local Machine] TO public
2> go
1> GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public
2> go
1> GRANT CONNECT ON ENDPOINT::[TSQL Local Machine] TO scott4
2> go
1> quit

C:\>sqlcmd -Uscott4 -PBailout6 -S tcp:scott-mcintosh2,65352
Msg 18456, Level 14, State 1, Server SCOTT-MCINTOSH2\SCOTTMCINTOSH, Line 1
Login failed for user 'scott4'.

But I do believe that the issue is privilege or permission related. This is interesting:

C:\>sqlcmd -Usa -PBailout6 -S tcp:localhost,65352 -w 700 -dmaster
1> ALTER SERVER ROLE sysadmin ADD MEMBER [scott4]
2> go
1> quit

C:\>sqlcmd -Uscott4 -PBailout6 -S tcp:localhost,65352
1> select getdate()
2> go

-----------------------
2013-09-10 17:35:24.353

... but I can't allow all my login accounts to be sysadmin.

Scott.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Well now you found the cause.
Something demands your users to be sysadmin. And I thing I know what it is.

Your user tryes to connect to a database after loggin in. And is prohibited.

Try:
as SA Create DB test, then
CREATE LOGIN scott3  WITH PASSWORD='Bailout6', DEFAULT_DATABASE = test
then
use TEST
and
CREATE USER scott3 FOR LOGIN scott3
now try your scott3 user

Regards Marten
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
JonahGroup!
Any feedback?

Regards Marten
0
JonahGroupAuthor Commented:
Thanks. Sorry for the response lag.

It didn't make a difference that there's a database user associated with the login, but it was worth a try. I seem to recall that in SQL Server 2008, you were allowed to have a login that wasn't a member of any database, and you could still login with the account.

The database creation code below was generated from "management studio", hence all the "alter" statements....

C:\>sqlcmd -Usa  -Pfoobar -S tcp:scott-mcintosh2,65352 -dmaster
1> CREATE DATABASE [CAHST]
2>  CONTAINMENT = NONE
3>  ON  PRIMARY
4> ( NAME = N'CAHST', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SCOTTMCINTOSH\MSSQL\DATA\CAHST.mdf' , SIZE = 1024000KB , FILEGROWTH = 102400KB )
5>  LOG ON
6> ( NAME = N'CAHST_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SCOTTMCINTOSH\MSSQL\DATA\CAHST_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
7> GO
1> ALTER DATABASE [CAHST] SET COMPATIBILITY_LEVEL = 110
2> GO
1> ALTER DATABASE [CAHST] SET ANSI_NULL_DEFAULT OFF
2> GO
1> ALTER DATABASE [CAHST] SET ANSI_NULLS OFF
2> GO
1> ALTER DATABASE [CAHST] SET ANSI_PADDING OFF
2> GO
1> ALTER DATABASE [CAHST] SET ANSI_WARNINGS OFF
2> GO
1> ALTER DATABASE [CAHST] SET ARITHABORT OFF
2> GO
1> ALTER DATABASE [CAHST] SET AUTO_CLOSE OFF
2> GO
1> ALTER DATABASE [CAHST] SET AUTO_CREATE_STATISTICS ON
2> GO
1> ALTER DATABASE [CAHST] SET AUTO_SHRINK OFF
2> GO
1> ALTER DATABASE [CAHST] SET AUTO_UPDATE_STATISTICS ON
2> GO
1> ALTER DATABASE [CAHST] SET CURSOR_CLOSE_ON_COMMIT OFF
2> GO
1> ALTER DATABASE [CAHST] SET CURSOR_DEFAULT  GLOBAL
2> GO
1> ALTER DATABASE [CAHST] SET CONCAT_NULL_YIELDS_NULL OFF
2> GO
1> ALTER DATABASE [CAHST] SET NUMERIC_ROUNDABORT OFF
2> GO
1> ALTER DATABASE [CAHST] SET QUOTED_IDENTIFIER OFF
2> GO
1> ALTER DATABASE [CAHST] SET RECURSIVE_TRIGGERS OFF
2> GO
1> ALTER DATABASE [CAHST] SET  DISABLE_BROKER
2> GO
1> ALTER DATABASE [CAHST] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
2> GO
1> ALTER DATABASE [CAHST] SET DATE_CORRELATION_OPTIMIZATION OFF
2> GO
1> ALTER DATABASE [CAHST] SET PARAMETERIZATION SIMPLE
2> GO
1> ALTER DATABASE [CAHST] SET READ_COMMITTED_SNAPSHOT OFF
2> GO
1> ALTER DATABASE [CAHST] SET  READ_WRITE
2> GO
1> ALTER DATABASE [CAHST] SET RECOVERY FULL
2> GO
1> ALTER DATABASE [CAHST] SET  MULTI_USER
2> GO
1> ALTER DATABASE [CAHST] SET PAGE_VERIFY CHECKSUM
2> GO
1> ALTER DATABASE [CAHST] SET TARGET_RECOVERY_TIME = 0 SECONDS
2> GO
1> USE [CAHST]
2> GO
Changed database context to 'CAHST'.
1> IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [CAHST] MODIFY FILEGROUP [PRIMARY] DEFAULT
2> GO
1> CREATE LOGIN scott5 WITH PASSWORD='Bailout6' , CHECK_EXPIRATION=OFF, DEFAULT_DATABASE=[CAHST]
2> go
1> GRANT CONNECT SQL TO scott5
2> go
1> GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO scott5
2> go
1> GRANT CONNECT ON ENDPOINT::[TSQL Local Machine] TO scott5
2> go
1> use CAHST
2> go
Changed database context to 'CAHST'.
1> CREATE USER scott5 FOR LOGIN scott5
2> go
1> quit

C:\>sqlcmd -Uscott5 -PBailout6 -S tcp:scott-mcintosh2,65352
Msg 18456, Level 14, State 1, Server SCOTT-MCINTOSH2\SCOTTMCINTOSH, Line 1
Login failed for user 'scott5'.

From the SQL Server errorlog:

2013-09-11 11:23:06.29 Logon       Error: 18456, Severity: 14, State: 12.
2013-09-11 11:23:06.29 Logon       Login failed for user 'scott5'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 127.0.0.1]
0
JonahGroupAuthor Commented:
I think we're getting closer to the cause. The demonstration below is very weird:

C:\>sqlcmd -Uscott5 -PBailout6 -S tcp:scott-mcintosh2,65352
Msg 18456, Level 14, State 1, Server SCOTT-MCINTOSH2\SCOTTMCINTOSH, Line 1
Login failed for user 'scott5'.

C:\>sqlcmd -Uscott5 -PBailout6 -S SCOTT-MCINTOSH2\SCOTTMCINTOSH
1> select getdate()
2> go

-----------------------
2013-09-11 13:57:38.820

(1 rows affected)
1>


Of course, most jdbc db tools connect via tcp/ip (host & port). So it's not an option to use host name and instance name.

What this means is that it's a security issue related to connecting via host+port over tcp. But I've already run this command to allow this:

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]

... and still the problem persists.

Scott.
0
JonahGroupAuthor Commented:
Additional info. I'm not sure whether this looks okay.

1> select class, class_desc=substring(class_desc,1,10),
2>      major_id, minor_id, grantee_principal_id, grantor_principal_id, type, permission_name=substring(permission_name,1,12),
3>      state, state_desc=substring(state_desc,1,16)
4> from sys.server_permissions where grantee_principal_id = suser_id('scott5')
5> go
class class_desc major_id    minor_id    grantee_principal_id grantor_principal_id type permission_name state state_desc
----- ---------- ----------- ----------- -------------------- -------------------- ---- --------------- ----- ----------------
  100 SERVER               0           0                  283                    1 COSQ CONNECT SQL     G     GRANT
  105 ENDPOINT             2           0                  283                    1 CO   CONNECT         G     GRANT
  105 ENDPOINT             4           0                  283                    1 CO   CONNECT         G     GRANT

(3 rows affected)
1>

Open in new window

0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
will:
sqlcmd -Uscott5 -PBailout6 -S"scott-mcintosh2,65352"
work, i e without the tcp: part?
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
if you restart the server, can you publish the errorlog here for me to look at

//Marten
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
By restart server I mean the SQL Server Service.

/Marten
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JonahGroupAuthor Commented:
I can't believe that I did this. The problem was the port. Your request to see the errorlog made me look at the entire log line-by-line very carefully after I restarted the server. I had picked up the DAC port and started using it, instead of the regular port.

When I use the correct port, instead of the DAC (dedicated admin connection) port, all is fine:

C:\>sqlcmd -Uscott5  -PBailout6 -S tcp:scott-mcintosh2,49275 -dmaster
1> select getdate()
2> go

-----------------------
2013-09-12 09:57:25.350

(1 rows affected)
1>

Open in new window

Attached is the errorlog.
ERRORLOG
0
JonahGroupAuthor Commented:
While restarting the server was not the solution, Marten led me to explore the terrain until I noticed in the errorlog that I was mistakenly using the DAC port to connect to the server. Of course it works fine for "sa", but as expected, it should not for other login accounts.

Thanks so much Marten!

Scott.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.