basic question about sa account on MSDE

basic question about sa account on MSDE

Post by Mark Minas » Mon, 01 Apr 2002 12:17:19



Hello --
I'm trying to make MSDE run so that it can host a database and table for
some ADO VBScripts on an ASP page.

I know that MSDE is working because I've started up Access from another
system and created both a table and a database.  I can see the resultant
files in the MSSQL7\DATA directory.

My problem is that I read all of these examples about ADO scripting to
connect to an MSDE/SQL-hosted database and when I try to use them, they
always fail with

"Login failed for user 'whatever name I use'

All examples refer to an "sa" account which MSDE's install most definitely
did NOT install.  So I created an sa account on the domain and made it the
logon account for both the MSSQLServer and SQLServerAgent.  I then made the
sa account a local administrator.  Overkill, yes, but I'm just trying to get
it to work, then I'll see how much I can pare down the permissions.

The login always fails.  I've tried a LOCAL account named sa that's an
admin, that fails.  I've tried a domain account that's a domain admin, that
fails.  I tried punching in the local admin account name and password, it
fails.  I am clearly missing something very very basic about creating this
sa account (and yes, I understand that "sa" is just the account name that
many people use, it could be anything).

If it helps, here's the sum total of the program that I'm trying to run:

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=DOGPC;DATABASE=ADP2;USER
ID=sa;PASSWORD=sa;"

That's with MSDE 1.0 and SP1 and SP2 for SQL installed.  On a Windows XP Pro
box.  Thanks in advance.

Mark Minasi

 
 
 

basic question about sa account on MSDE

Post by Allan Mitche » Mon, 01 Apr 2002 18:10:44


OK.

I thnk you've got the wrong end of the stick here

sa IS installed by SQL Server.  sa is a SQl Server account not NT.  the default password on
installation is nothing.  You are running SQl Server 7 which defaults to installation in Mixed Mode
whereas SQL Server 2K defaults to Windows Authentication.

MSDE as you know has no FE so you need to roll  your own.  My personal favourites are VB and the
SQLDMO library.  Go to my site for more on DMO www.allisonmitchell.com/forums and look in the
articles and code sections

The SQL Server Services on every one of my installations are a domain account and are local admin on
the particular box as well.

If you have created an account called sa on the domain then try logging into your PC using this
account and change your connection string to

cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=DOGPC;DATABASE=ADP2;Integrated Security=SSPI;"

Allan


>Hello --
>I'm trying to make MSDE run so that it can host a database and table for
>some ADO VBScripts on an ASP page.

>I know that MSDE is working because I've started up Access from another
>system and created both a table and a database.  I can see the resultant
>files in the MSSQL7\DATA directory.

>My problem is that I read all of these examples about ADO scripting to
>connect to an MSDE/SQL-hosted database and when I try to use them, they
>always fail with

>"Login failed for user 'whatever name I use'

>All examples refer to an "sa" account which MSDE's install most definitely
>did NOT install.  So I created an sa account on the domain and made it the
>logon account for both the MSSQLServer and SQLServerAgent.  I then made the
>sa account a local administrator.  Overkill, yes, but I'm just trying to get
>it to work, then I'll see how much I can pare down the permissions.

>The login always fails.  I've tried a LOCAL account named sa that's an
>admin, that fails.  I've tried a domain account that's a domain admin, that
>fails.  I tried punching in the local admin account name and password, it
>fails.  I am clearly missing something very very basic about creating this
>sa account (and yes, I understand that "sa" is just the account name that
>many people use, it could be anything).

>If it helps, here's the sum total of the program that I'm trying to run:

>Set cn = Server.CreateObject("ADODB.Connection")
>cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=DOGPC;DATABASE=ADP2;USER
>ID=sa;PASSWORD=sa;"

>That's with MSDE 1.0 and SP1 and SP2 for SQL installed.  On a Windows XP Pro
>box.  Thanks in advance.

>Mark Minasi

Allan Mitchell
-----------------------
www.allisonmitchell.com
Visit the site for DMO and DTS code and articles

 
 
 

basic question about sa account on MSDE

Post by Mark Minas » Mon, 01 Apr 2002 22:15:14


Thanks, Allan.  I had no idea...  Let me summarize this so that others can
benefit.  (Unless I am already the dumbest guy in the database world, in
which case they won't.<g>)

Apparently I see now that

    (1) SQL Server (or in my case MSDE) actually maintains its OWN set of
user accounts, not integrated with the SAM/AD?  Wow.  Thank you, I would
have NEVER guessed that.  As an OS guy, I'm constantly hearing about how the
NT 4 domain SAMs and W2K ADs integrate accounts and security.  Stunning.

    (2) So there is already an MSDE-created-and-maintained sa account on my
system with password blank?  Sonuvagun, there is -- this works:
<%
set cn=server.createobject("ADODB.Connection")
cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=DOGPC;DATABASE=ADP2SQL;USER
ID=sa;PWD=;"
cmdline="SELECT * FROM subscribers"   ' subscribers is a table in ADPSQL2
set rs=server.createobject("ADODB.recordset")
set rs=cn.execute(cmdline)
%>

Now I seem to be able to do the usual stuff with this recordset.

    (3) That does NOT work, however, when executed on an IIS server that is
not on the same machine as the MSDE machine.  That seems to make sense, as I
wouldn't want just any dodo being able to set up an IIS server with ASP
pages that manipulate my database.  To do that, I guess I would have to
grant the same powers to some domain account as MSDE automatically granted
to the sa account.  Which means...

Question 1:  does this mean that there is some kind of manager program that
lets me create MSDE accounts, grant them powers, and (if I'm lucky) grant
powers to regular old domain or local accounts instead of MSDE-created
accounts?

My guess is that this is the "enterprise manager" that I see references to.
Is that what you meant when you said I'd have to cook up my own "FE,"
whatever that is?  Is there a command-line tool of some kind that will do
it?

Thanks again.

Mark


Quote:> OK.

> I thnk you've got the wrong end of the stick here

> sa IS installed by SQL Server.  sa is a SQl Server account not NT.  the
default password on
> installation is nothing.  You are running SQl Server 7 which defaults to

installation in Mixed Mode
Quote:> whereas SQL Server 2K defaults to Windows Authentication.

> MSDE as you know has no FE so you need to roll  your own.  My personal

favourites are VB and the
Quote:> SQLDMO library.  Go to my site for more on DMO

www.allisonmitchell.com/forums and look in the
Quote:> articles and code sections

> The SQL Server Services on every one of my installations are a domain

account and are local admin on
Quote:> the particular box as well.

> If you have created an account called sa on the domain then try logging

into your PC using this
> account and change your connection string to

> cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=DOGPC;DATABASE=ADP2;Integrated
Security=SSPI;"

> Allan


> >Hello --
> >I'm trying to make MSDE run so that it can host a database and table for
> >some ADO VBScripts on an ASP page.

> >I know that MSDE is working because I've started up Access from another
> >system and created both a table and a database.  I can see the resultant
> >files in the MSSQL7\DATA directory.

> >My problem is that I read all of these examples about ADO scripting to
> >connect to an MSDE/SQL-hosted database and when I try to use them, they
> >always fail with

> >"Login failed for user 'whatever name I use'

> >All examples refer to an "sa" account which MSDE's install most
definitely
> >did NOT install.  So I created an sa account on the domain and made it
the
> >logon account for both the MSSQLServer and SQLServerAgent.  I then made
the
> >sa account a local administrator.  Overkill, yes, but I'm just trying to
get
> >it to work, then I'll see how much I can pare down the permissions.

> >The login always fails.  I've tried a LOCAL account named sa that's an
> >admin, that fails.  I've tried a domain account that's a domain admin,
that
> >fails.  I tried punching in the local admin account name and password, it
> >fails.  I am clearly missing something very very basic about creating
this
> >sa account (and yes, I understand that "sa" is just the account name that
> >many people use, it could be anything).

> >If it helps, here's the sum total of the program that I'm trying to run:

> >Set cn = Server.CreateObject("ADODB.Connection")
> >cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=DOGPC;DATABASE=ADP2;USER
> >ID=sa;PASSWORD=sa;"

> >That's with MSDE 1.0 and SP1 and SP2 for SQL installed.  On a Windows XP
Pro
> >box.  Thanks in advance.

> >Mark Minasi

> Allan Mitchell
> -----------------------
> www.allisonmitchell.com
> Visit the site for DMO and DTS code and articles

 
 
 

basic question about sa account on MSDE

Post by Allan Mitche » Tue, 02 Apr 2002 05:48:00


Mark,

        There is a command line tool called OSQL.exe.  Do you not have an edition of Enterprise
Manager that you can register the MSDE version of SQL Server in.  FE = Front End

You could install client tools only from a SQl Server CD to administer your database

Quick demo of DMO

dim oSQLServer as SQLDMO.SQLServer
dim oLogin as SQLDMO.Login

set oSQLServer = new SQLDMO.SQLServer

oSQLServer.Connect "Name of Server","sa",""

for Each oLogin in oSQLServer.logins
        msgbox oLogin.Name
next oLogin

BTW

put a password on the sa account.  If you get in an sa you can do anything you want.

if oLogin.Name = "sa" then
        oLogin.SetPassword "", 'gobbledyduke"
end if

Allan


>Thanks, Allan.  I had no idea...  Let me summarize this so that others can
>benefit.  (Unless I am already the dumbest guy in the database world, in
>which case they won't.<g>)

>Apparently I see now that

>    (1) SQL Server (or in my case MSDE) actually maintains its OWN set of
>user accounts, not integrated with the SAM/AD?  Wow.  Thank you, I would
>have NEVER guessed that.  As an OS guy, I'm constantly hearing about how the
>NT 4 domain SAMs and W2K ADs integrate accounts and security.  Stunning.

>    (2) So there is already an MSDE-created-and-maintained sa account on my
>system with password blank?  Sonuvagun, there is -- this works:
><%
>set cn=server.createobject("ADODB.Connection")
>cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=DOGPC;DATABASE=ADP2SQL;USER
>ID=sa;PWD=;"
>cmdline="SELECT * FROM subscribers"   ' subscribers is a table in ADPSQL2
>set rs=server.createobject("ADODB.recordset")
>set rs=cn.execute(cmdline)
>%>

>Now I seem to be able to do the usual stuff with this recordset.

>    (3) That does NOT work, however, when executed on an IIS server that is
>not on the same machine as the MSDE machine.  That seems to make sense, as I
>wouldn't want just any dodo being able to set up an IIS server with ASP
>pages that manipulate my database.  To do that, I guess I would have to
>grant the same powers to some domain account as MSDE automatically granted
>to the sa account.  Which means...

>Question 1:  does this mean that there is some kind of manager program that
>lets me create MSDE accounts, grant them powers, and (if I'm lucky) grant
>powers to regular old domain or local accounts instead of MSDE-created
>accounts?

>My guess is that this is the "enterprise manager" that I see references to.
>Is that what you meant when you said I'd have to cook up my own "FE,"
>whatever that is?  Is there a command-line tool of some kind that will do
>it?

>Thanks again.

>Mark



>> OK.

>> I thnk you've got the wrong end of the stick here

>> sa IS installed by SQL Server.  sa is a SQl Server account not NT.  the
>default password on
>> installation is nothing.  You are running SQl Server 7 which defaults to
>installation in Mixed Mode
>> whereas SQL Server 2K defaults to Windows Authentication.

>> MSDE as you know has no FE so you need to roll  your own.  My personal
>favourites are VB and the
>> SQLDMO library.  Go to my site for more on DMO
>www.allisonmitchell.com/forums and look in the
>> articles and code sections

>> The SQL Server Services on every one of my installations are a domain
>account and are local admin on
>> the particular box as well.

>> If you have created an account called sa on the domain then try logging
>into your PC using this
>> account and change your connection string to

>> cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=DOGPC;DATABASE=ADP2;Integrated
>Security=SSPI;"

>> Allan


>> >Hello --
>> >I'm trying to make MSDE run so that it can host a database and table for
>> >some ADO VBScripts on an ASP page.

>> >I know that MSDE is working because I've started up Access from another
>> >system and created both a table and a database.  I can see the resultant
>> >files in the MSSQL7\DATA directory.

>> >My problem is that I read all of these examples about ADO scripting to
>> >connect to an MSDE/SQL-hosted database and when I try to use them, they
>> >always fail with

>> >"Login failed for user 'whatever name I use'

>> >All examples refer to an "sa" account which MSDE's install most
>definitely
>> >did NOT install.  So I created an sa account on the domain and made it
>the
>> >logon account for both the MSSQLServer and SQLServerAgent.  I then made
>the
>> >sa account a local administrator.  Overkill, yes, but I'm just trying to
>get
>> >it to work, then I'll see how much I can pare down the permissions.

>> >The login always fails.  I've tried a LOCAL account named sa that's an
>> >admin, that fails.  I've tried a domain account that's a domain admin,
>that
>> >fails.  I tried punching in the local admin account name and password, it
>> >fails.  I am clearly missing something very very basic about creating
>this
>> >sa account (and yes, I understand that "sa" is just the account name that
>> >many people use, it could be anything).

>> >If it helps, here's the sum total of the program that I'm trying to run:

>> >Set cn = Server.CreateObject("ADODB.Connection")
>> >cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=DOGPC;DATABASE=ADP2;USER
>> >ID=sa;PASSWORD=sa;"

>> >That's with MSDE 1.0 and SP1 and SP2 for SQL installed.  On a Windows XP
>Pro
>> >box.  Thanks in advance.

>> >Mark Minasi

>> Allan Mitchell
>> -----------------------
>> www.allisonmitchell.com
>> Visit the site for DMO and DTS code and articles

Allan Mitchell
-----------------------
www.allisonmitchell.com
Visit the site for DMO and DTS code and articles
 
 
 

basic question about sa account on MSDE

Post by Shri Anandpura [M » Sat, 06 Apr 2002 04:09:37


Thanks Allan and Mark,  for posting on our newsgroups!
Thanks
Shri Anandpura

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.

--------------------

| Subject: Re: basic question about sa account on MSDE
| Date: Sun, 31 Mar 2002 20:48:00 GMT



| X-Newsreader: Forte Free Agent 1.21/32.243
| Newsgroups: microsoft.public.sqlserver.setup
| NNTP-Posting-Host: host62-172-168-72.btinternet.com 62.172.168.72
| Lines: 1        
| Path: cpmsftngxa07!tkmsftngxs01!tkmsftngp01!tkmsftngp04
| Xref: cpmsftngxa07 microsoft.public.sqlserver.setup:39021
| X-Tomcat-NG: microsoft.public.sqlserver.setup
|
| Mark,
|
|       There is a command line tool called OSQL.exe.  Do you not have an
edition of Enterprise
| Manager that you can register the MSDE version of SQL Server in.  FE =
Front End
|
| You could install client tools only from a SQl Server CD to administer
your database
|
|
| Quick demo of DMO
|
| dim oSQLServer as SQLDMO.SQLServer
| dim oLogin as SQLDMO.Login
|
| set oSQLServer = new SQLDMO.SQLServer
|
| oSQLServer.Connect "Name of Server","sa",""
|
|
| for Each oLogin in oSQLServer.logins
|       msgbox oLogin.Name
| next oLogin
|
| BTW
|
| put a password on the sa account.  If you get in an sa you can do
anything you want.
|
| if oLogin.Name = "sa" then
|       oLogin.SetPassword "", 'gobbledyduke"
| end if
|
|
| Allan
|
|
|
|
|

|
| >Thanks, Allan.  I had no idea...  Let me summarize this so that others
can
| >benefit.  (Unless I am already the dumbest guy in the database world, in
| >which case they won't.<g>)
| >
| >Apparently I see now that
| >
| >    (1) SQL Server (or in my case MSDE) actually maintains its OWN set of
| >user accounts, not integrated with the SAM/AD?  Wow.  Thank you, I would
| >have NEVER guessed that.  As an OS guy, I'm constantly hearing about how
the
| >NT 4 domain SAMs and W2K ADs integrate accounts and security.  Stunning.
| >
| >    (2) So there is already an MSDE-created-and-maintained sa account on
my
| >system with password blank?  Sonuvagun, there is -- this works:
| ><%
| >set cn=server.createobject("ADODB.Connection")
| >cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=DOGPC;DATABASE=ADP2SQL;USER
| >ID=sa;PWD=;"
| >cmdline="SELECT * FROM subscribers"   ' subscribers is a table in ADPSQL2
| >set rs=server.createobject("ADODB.recordset")
| >set rs=cn.execute(cmdline)
| >%>
| >
| >Now I seem to be able to do the usual stuff with this recordset.
| >
| >    (3) That does NOT work, however, when executed on an IIS server that
is
| >not on the same machine as the MSDE machine.  That seems to make sense,
as I
| >wouldn't want just any dodo being able to set up an IIS server with ASP
| >pages that manipulate my database.  To do that, I guess I would have to
| >grant the same powers to some domain account as MSDE automatically
granted
| >to the sa account.  Which means...
| >
| >Question 1:  does this mean that there is some kind of manager program
that
| >lets me create MSDE accounts, grant them powers, and (if I'm lucky) grant
| >powers to regular old domain or local accounts instead of MSDE-created
| >accounts?
| >
| >My guess is that this is the "enterprise manager" that I see references
to.
| >Is that what you meant when you said I'd have to cook up my own "FE,"
| >whatever that is?  Is there a command-line tool of some kind that will do
| >it?
| >
| >Thanks again.
| >
| >Mark
| >
| >


| >> OK.
| >>
| >> I thnk you've got the wrong end of the stick here
| >>
| >>
| >> sa IS installed by SQL Server.  sa is a SQl Server account not NT.  the
| >default password on
| >> installation is nothing.  You are running SQl Server 7 which defaults
to
| >installation in Mixed Mode
| >> whereas SQL Server 2K defaults to Windows Authentication.
| >>
| >> MSDE as you know has no FE so you need to roll  your own.  My personal
| >favourites are VB and the
| >> SQLDMO library.  Go to my site for more on DMO
| >www.allisonmitchell.com/forums and look in the
| >> articles and code sections
| >>
| >> The SQL Server Services on every one of my installations are a domain
| >account and are local admin on
| >> the particular box as well.
| >>
| >> If you have created an account called sa on the domain then try logging
| >into your PC using this
| >> account and change your connection string to
| >>
| >>
| >> cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=DOGPC;DATABASE=ADP2;Integrated
| >Security=SSPI;"
| >>
| >> Allan
| >>
| >>


| >>
| >> >Hello --
| >> >I'm trying to make MSDE run so that it can host a database and table
for
| >> >some ADO VBScripts on an ASP page.
| >> >
| >> >I know that MSDE is working because I've started up Access from
another
| >> >system and created both a table and a database.  I can see the
resultant
| >> >files in the MSSQL7\DATA directory.
| >> >
| >> >My problem is that I read all of these examples about ADO scripting to
| >> >connect to an MSDE/SQL-hosted database and when I try to use them,
they
| >> >always fail with
| >> >
| >> >"Login failed for user 'whatever name I use'
| >> >
| >> >All examples refer to an "sa" account which MSDE's install most
| >definitely
| >> >did NOT install.  So I created an sa account on the domain and made it
| >the
| >> >logon account for both the MSSQLServer and SQLServerAgent.  I then
made
| >the
| >> >sa account a local administrator.  Overkill, yes, but I'm just trying
to
| >get
| >> >it to work, then I'll see how much I can pare down the permissions.
| >> >
| >> >The login always fails.  I've tried a LOCAL account named sa that's an
| >> >admin, that fails.  I've tried a domain account that's a domain admin,
| >that
| >> >fails.  I tried punching in the local admin account name and
password, it
| >> >fails.  I am clearly missing something very very basic about creating
| >this
| >> >sa account (and yes, I understand that "sa" is just the account name
that
| >> >many people use, it could be anything).
| >> >
| >> >If it helps, here's the sum total of the program that I'm trying to
run:
| >> >
| >> >Set cn = Server.CreateObject("ADODB.Connection")
| >> >cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=DOGPC;DATABASE=ADP2;USER
| >> >ID=sa;PASSWORD=sa;"
| >> >
| >> >That's with MSDE 1.0 and SP1 and SP2 for SQL installed.  On a Windows
XP
| >Pro
| >> >box.  Thanks in advance.
| >> >
| >> >Mark Minasi
| >> >
| >> >
| >>
| >>
| >>
| >>
| >> Allan Mitchell
| >> -----------------------
| >> www.allisonmitchell.com
| >> Visit the site for DMO and DTS code and articles
| >
| >
|
|
|
|
| Allan Mitchell
| -----------------------
| www.allisonmitchell.com
| Visit the site for DMO and DTS code and articles
|

 
 
 

1. MSDE Blank SA Account.

Hello,

Whats the easiest way of securing the SA account when installing MSDE? By
default it has a blank SA password and no way of changing it without SQL EA
Tools.

Can we stop it enabling the TCP port, can we assign an SA account password
on install or can we change it.

Ideally this should be without having to install SQL EA Tools.

Regards

Steve

2. Help needed copying recordset contents to table

3. SA account and SA role

4. Query

5. Basic MSDE questions

6. Query help

7. MSDE basic question

8. Y2K Tool for Informix, Oracle, Sybase, MS SQL Server, SQL Anywhere

9. Installing SQL2000 SP1 kills sa and builtin\administrator accounts

10. sp_addumpdevice with non-sa account?

11. How to give sa-privileges to an account?

12. SA Account (SQL6.5)

13. Authentication Error using SA account