Error: 18456, Severity: 14, State: 16

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/pr...sqlexpress.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
AlastairDoes the password have any blank spaces or double quotes ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"Alastair" <alastair_anderson@.hotmail.com> wrote in message
news:1174642161.471601.110030@.y80g2000hsf.googlegroups.com...
> 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/pr...sqlexpress.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
>|||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...@.hotmail.com> wrote in message
> news:1174642161.471601.110030@.y80g2000hsf.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - 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?|||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_anderson@.hotmail.com> wrote in message
news:1174655306.297982.325260@.b75g2000hsg.googlegroups.com...
> On 23 Mar, 13:00, "Jens K. Suessmeyer" <J...@.NoSpamhere-
> sqlserver2005.de> wrote:
> The only double quotes that it contains are those that surround the
> password in the .bat file.
> Should these be single quotes?
>|||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 tha
t
> 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...@.hotmail.com> wrote in message
> news:1174655306.297982.325260@.b75g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
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|||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 tha
t
> 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...@.hotmail.com> wrote in message
> news:1174655306.297982.325260@.b75g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - 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?|||No, sa has by default access to everything, including the internal data.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"Alastair" <alastair_anderson@.hotmail.com> wrote in message
news:1174779869.212689.303860@.o5g2000hsb.googlegroups.com...
> On 23 Mar, 21:26, "Jens K. Suessmeyer" <J...@.NoSpamhere-
> sqlserver2005.de> wrote:
> 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?
>