sql server connect: Error: 18456, Severity: 14, State: 16 -- sql server connect

Error: 18456, Severity: 14, State: 16

Alastair
3/23/2007 2:29:21 AM

Hi All,

I am getting the following error in the ERRORLOG File when I am trying
to connect to SQL Server 2005 Express Edition from the command line.

2007-03-23 07:30:05.54 Logon Error: 18456, Severity: 14, State:
16.
2007-03-23 07:30:05.54 Logon Login failed for user 'sa'.
[CLIENT: xx.xx.xx.xx]

I have a fresh install of SQL Server on my local machine, which is
probably the source of my angst, but I am trying to create a database
from scratch through using a batch file that is run from the command
line, the batch file looks like this:

<snip>
SQLCMD -U %USR% -P %PWD% -S %SVR% -d %DBF% -i CreateDatabase.sql
<snip>

The values of %USR% and %PWD% are correct as I frequently log into
Management Studio with the details, %SVR% is "<ComputerName>
\SQLEXPRESS,1433" and %DBF% is <DatabaseName>. The contents of
CreateDatabase.sql when run in Management Studio successfully creates
the database that I require so I know the error doesn't lie there.

The command line error that is showing reads as the following:

Msg 4060, Level 11, State 1, Server <ComputerName>\SQLEXPRESS, Line 1
Cannot open database "<DatabaseName>" requested by the login. The
login failed.
Msg 18456, Level 14, State 1, Server <ComputerName>\SQLEXPRESS, Line 1
Login failed for user 'sa'.

I have a feeling that it is related to 'sa' not having the correct
permissions with regards to logging into or creating a database from
scratch, but I find it odd that I can login as 'sa' in Management
Studio and create the database from there, but when I try it from the
command-line (despite having TCP/IP enabled) it won't let me connect
to create a database.

Is there something I am missing here? I have been banging my head off
of walls for days over this and am unsure of the next steps to take.

If any of this screams "why the hell are you doing this?" then please
let me know as at the moment I am punching in the dark with this
problem, I have searched Usenet, Google Groups, the Web and although I
have found potential solutions to this particular problem I am having,
nothing I have done so far has actually managed to fix the issue.

Related articles I have followed are:

Login failed for user 'x'
http://msdn2.microsoft.com/en-us/library/ms366351.aspx

Change Server Authentication Mode
http://msdn2.microsoft.com/en-us/library/ms188670.aspx

Upgrading MSDE 2000 to SQL Server 2005 Express [Enabling network
protocols section]
http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx

NOTE: I just listed these articles to show what path I am currently
looking at with regards to this problem, but again I stress I have
found nothing yet to get round this problem, and any further articles
or direction would be much appreciated.

Yours Distressingly

Alastair

Re: Error: 18456, Severity: 14, State: 16

Alastair
3/23/2007 6:08:26 AM

On 23 Mar, 13:00, "Jens K. Suessmeyer" <J...@NoSpamhere-
[quoted text, click to view]

The only double quotes that it contains are those that surround the
password in the .bat file.

Should these be single quotes?

Re: Error: 18456, Severity: 14, State: 16

Jens K. Suessmeyer
3/23/2007 10:26:17 PM

Normally this should work, did you try conencting with the same syntax that
you are using in the batch file via an interactive commandprompt ? Did you
try to leave out the quotes ?

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

[quoted text, click to view]

Re: Error: 18456, Severity: 14, State: 16

Alastair
3/24/2007 3:44:23 AM

On 23 Mar, 21:26, "Jens K. Suessmeyer" <J...@NoSpamhere-
[quoted text, click to view]

Hi Jens,

Thanks for the input on this it is very much appreciated,

I tried running the batch file without quotations around the strings
and I still get the same error, I also tried single quotes, but the
command-line processor was interpreting them as part of the strings.

I took your advice and tried running it straight from the command-line
without using the .bat file, and still the same error came screaming
back at me.

Alastair

Re: Error: 18456, Severity: 14, State: 16

Alastair
3/24/2007 4:44:29 PM

On 23 Mar, 21:26, "Jens K. Suessmeyer" <J...@NoSpamhere-
[quoted text, click to view]

I've done some further hunting around for solutions, could it
potentially be related to "sa" not having any permissions in the
SYS.DATABASE_PERMISSIONS table?

Re: Error: 18456, Severity: 14, State: 16

Jens K. Suessmeyer
3/26/2007 6:22:34 AM

No, sa has by default access to everything, including the internal data.

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server connect group.