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

On 23 Mar, 21:26, "Jens K. Suessmeyer" <J...@NoSpamhere-
sqlserver2005.de> wrote:
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
---

"Alastair" <alastair_ander...@xxxxxxxxxxx> wrote in message

news:1174655306.297982.325260@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



On 23 Mar, 13:00, "Jens K. Suessmeyer" <J...@NoSpamhere-
sqlserver2005.de> wrote:
Does the password have any blank spaces or double quotes ?

Jens K. Suessmeyer.

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

"Alastair" <alastair_ander...@xxxxxxxxxxx> wrote in message

news:1174642161.471601.110030@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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...

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- Hide quoted text -

- Show quoted text -

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

Should these be single quotes?- Hide quoted text -

- Show quoted text -

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?

.

Relevant Pages

  • Re: problem with a batch file wkgrp parameter
    ... The short answer is you're using the wrong syntax in your batch file. ... that isn't the secured workgroup information file used to secure the database, ... permission to open this secured database. ... You need to remove the "START /MAX" command, but even then it won't work ...
    (microsoft.public.access.security)
  • Re: problem with a batch file wkgrp parameter
    ... The short answer is you're using the wrong syntax in your batch file. ... that isn't the secured workgroup information file used to secure the database, ... permission to open this secured database. ... You need to remove the "START /MAX" command, but even then it won't work ...
    (microsoft.public.access.security)
  • Re: problem with a batch file wkgrp parameter
    ... The short answer is you're using the wrong syntax in your batch file. ... that isn't the secured workgroup information file used to secure the database, ... permission to open this secured database. ... You need to remove the "START /MAX" command, but even then it won't work ...
    (microsoft.public.access.security)
  • Re: Error: 18456, Severity: 14, State: 16
    ... did you try conencting with the same syntax that you are using in the batch file via an interactive commandprompt? ... > to connect to SQL Server 2005 Express Edition from the command line. ... > the database that I require so I know the error doesn't lie there. ... I just listed these articles to show what path I am currently ...
    (microsoft.public.sqlserver.connect)
  • Error: 18456, Severity: 14, State: 16
    ... to connect to SQL Server 2005 Express Edition from the command line. ... CreateDatabase.sql when run in Management Studio successfully creates ... the database that I require so I know the error doesn't lie there. ...
    (microsoft.public.sqlserver.connect)