sa not working

19 Oct 2006 5:32 AM

Charles A. Lackman

Hello,

I installed SQL Express in a silent install using the following:

SQLEXPR.EXE /qb addlocal=all instancename=SQLExpress SAPWD=MyPwd
Disablenetworkprotocols = 0 SQLAUTOSTART=1

The install works just the way I want it to.

But when I try to connect to it using ADO.NET or from sqlcmd -U sa -P
MyPwd -S MyServer\SQLExpress

I receive the following Error:

Login failed for user 'sa'. The user is not associated with a trusted SQL
Server connection.

To fix the problem I have tried:

ALTER LOGIN sa ENABLED;
GO
ALTER LOGIN sa WITH PASSWORD = 'MyPwd';
GO

This did not work either,

Please help,

Thanks,

Chuck

19 Oct 2006 5:55 AM

soonyu

you SQL server security is Windows Authentication mode?
If you want connect use sql login, the security mode sould be SQL Server and
Windows authentication mode.

Login to SQL server through SSMS using windows authentication.
Click on server, property, Security and check on the Server authentication
mode.

Cheers

Show quoteHide quote

"Charles A. Lackman" wrote:

> Hello,
>
> I installed SQL Express in a silent install using the following:
>
> SQLEXPR.EXE /qb addlocal=all instancename=SQLExpress SAPWD=MyPwd
> Disablenetworkprotocols = 0 SQLAUTOSTART=1
>
> The install works just the way I want it to.
>
> But when I try to connect to it using ADO.NET or from sqlcmd -U sa -P
> MyPwd -S MyServer\SQLExpress
>
> I receive the following Error:
>
> Login failed for user 'sa'. The user is not associated with a trusted SQL
> Server connection.
>
> To fix the problem I have tried:
>
> ALTER LOGIN sa ENABLED;
> GO
> ALTER LOGIN sa WITH PASSWORD = 'MyPwd';
> GO
>
> This did not work either,
>
> Please help,
>
> Thanks,
>
> Chuck
>
>
>

19 Oct 2006 6:03 AM

Charles A. Lackman

Thanks for your reply,

How would I rewrite my Silent install to fix the problem. The computer that
has this installed does not have SSMS installed. I am testing a deployment
of a application and the client computers will not have that installed.

Doesn't SAWPD set the install to Mixed Authentication?

Chuck

"soonyu" <soo***@discussions.microsoft.com> wrote in message
news:00DE8810-EB97-4001-A0C0-5BFDDB2857C6@microsoft.com...

you SQL server security is Windows Authentication mode?
If you want connect use sql login, the security mode sould be SQL Server and
Windows authentication mode.

Login to SQL server through SSMS using windows authentication.
Click on server, property, Security and check on the Server authentication
mode.

Cheers

Show quoteHide quote

"Charles A. Lackman" wrote:

> Hello,
>
> I installed SQL Express in a silent install using the following:
>
> SQLEXPR.EXE /qb addlocal=all instancename=SQLExpress SAPWD=MyPwd
> Disablenetworkprotocols = 0 SQLAUTOSTART=1
>
> The install works just the way I want it to.
>
> But when I try to connect to it using ADO.NET or from sqlcmd -U sa -P
> MyPwd -S MyServer\SQLExpress
>
> I receive the following Error:
>
> Login failed for user 'sa'. The user is not associated with a trusted SQL
> Server connection.
>
> To fix the problem I have tried:
>
> ALTER LOGIN sa ENABLED;
> GO
> ALTER LOGIN sa WITH PASSWORD = 'MyPwd';
> GO
>
> This did not work either,
>
> Please help,
>
> Thanks,
>
> Chuck
>
>
>

19 Oct 2006 9:58 AM

Andrea Montanari

hi Charles,
Charles A. Lackman wrote:

> Thanks for your reply,
>
> How would I rewrite my Silent install to fix the problem. The
> computer that has this installed does not have SSMS installed. I am
> testing a deployment of a application and the client computers will
> not have that installed.
>
> Doesn't SAWPD set the install to Mixed Authentication?

nope... it's just a convenient way to protect sa login with a strong
password, even in scenarios where the only available authentication is
WindowsNT...
as you saw from Arnie advice, it's very simple to modify that setting after
installation (even if this still requires admin privileges on the target
machine)..
if you want to specify Mexed Authentication at installation time, you have
to provide an additional parameter, SECURITYMODE=SQL, as you can see from
the supported parameters list at
http://msdn2.microsoft.com/en-us/library/ms144259.aspx

--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
--------- remove DMO to reply

19 Oct 2006 5:55 AM

Arnie Rowland

I believe that the default installation does not enable SQL logins.

You can either use Management Studio (Server\Properties\Security) to change
the security model, or change this Registry setting:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft Sql
Server\MSSQL.1\MSSqlServer\LoginMode

Change the value from 1 to 2 to enable SQL Logins

See this article for more information:

http://support.microsoft.com/kb/319930

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quoteHide quote

"Charles A. Lackman" <Char***@CreateItSoftware.net> wrote in message
news:edFez$z8GHA.2316@TK2MSFTNGP04.phx.gbl...
> Hello,
>
> I installed SQL Express in a silent install using the following:
>
> SQLEXPR.EXE /qb addlocal=all instancename=SQLExpress SAPWD=MyPwd
> Disablenetworkprotocols = 0 SQLAUTOSTART=1
>
> The install works just the way I want it to.
>
> But when I try to connect to it using ADO.NET or from sqlcmd -U sa -P
> MyPwd -S MyServer\SQLExpress
>
> I receive the following Error:
>
> Login failed for user 'sa'. The user is not associated with a trusted SQL
> Server connection.
>
> To fix the problem I have tried:
>
> ALTER LOGIN sa ENABLED;
> GO
> ALTER LOGIN sa WITH PASSWORD = 'MyPwd';
> GO
>
> This did not work either,
>
> Please help,
>
> Thanks,
>
> Chuck
>
>

19 Oct 2006 5:59 AM

Uri Dimant

Charles
Read this article , despite they talk about MSDE 2000 I think it could be a
reason for SQL Server 2005 Express Edition
http://support.microsoft.com/kb/321698/

Show quoteHide quote

"Charles A. Lackman" <Char***@CreateItSoftware.net> wrote in message
news:edFez$z8GHA.2316@TK2MSFTNGP04.phx.gbl...
> Hello,
>
> I installed SQL Express in a silent install using the following:
>
> SQLEXPR.EXE /qb addlocal=all instancename=SQLExpress SAPWD=MyPwd
> Disablenetworkprotocols = 0 SQLAUTOSTART=1
>
> The install works just the way I want it to.
>
> But when I try to connect to it using ADO.NET or from sqlcmd -U sa -P
> MyPwd -S MyServer\SQLExpress
>
> I receive the following Error:
>
> Login failed for user 'sa'. The user is not associated with a trusted SQL
> Server connection.
>
> To fix the problem I have tried:
>
> ALTER LOGIN sa ENABLED;
> GO
> ALTER LOGIN sa WITH PASSWORD = 'MyPwd';
> GO
>
> This did not work either,
>
> Please help,
>
> Thanks,
>
> Chuck
>
>

Thread options