MS SQL Server - cannot connect 2005 using sa with ASP.NET

Author

cannot connect 2005 using sa with ASP.NET

Les Caudle

2006-03-29, 8:23 pm

I un-installed SQL 2000 Developer's version - and installed SQL 2005 developer's
version in mixed mode with the same sa password I'd used previously.

I then attached all my previous databases.

I can connect to SQL 2005 using the Management Studio using the sa user/pass.

However, the ASP.NET 2.0 apps cannot connect using the sa login.

I tried going into the login properties for sa, and checking the 'map' checkbox
under 'User Mapping'.

It objects to using the special principal 'sa'.

I notice that if I go into the security settings for the database, the dbo is
mapped to local user's login name (whereas on another SQL 2000 box, dbo has a
login name of 'sa'.

How can I get my sa user/pass to work again? I guess I haven't dealt with these
issues in so long I've forgotten something very obvious and basic.
--
Thanks in advance, Les Caudle

Michael Abraham

2006-03-30, 7:23 am

Make sure SQL2005 is configured to accept non-local connections - see the
Surface Area Configuration utility.

HTH,

Mike
"Les Caudle" <very@tiredofspam.com> wrote in message
news:6nnl22hvftmvqma
gp3cke7m3j31uhsnivt@
4ax.com...
>I un-installed SQL 2000 Developer's version - and installed SQL 2005
>developer's
> version in mixed mode with the same sa password I'd used previously.
>
> I then attached all my previous databases.
>
> I can connect to SQL 2005 using the Management Studio using the sa
> user/pass.
>
> However, the ASP.NET 2.0 apps cannot connect using the sa login.
>
> I tried going into the login properties for sa, and checking the 'map'
> checkbox
> under 'User Mapping'.
>
> It objects to using the special principal 'sa'.
>
> I notice that if I go into the security settings for the database, the dbo
> is
> mapped to local user's login name (whereas on another SQL 2000 box, dbo
> has a
> login name of 'sa'.
>
> How can I get my sa user/pass to work again? I guess I haven't dealt with
> these
> issues in so long I've forgotten something very obvious and basic.
> --
> Thanks in advance, Les Caudle

Les Caudle

2006-03-30, 11:23 am

Mike - SQL2005 is located on the same box as the web server.

I think the problem has to do with security permissions. For some reason, the
sa user/pass seems unable to be used to open the database.

This doesn't make sense to me, as I connected to the Management Studio using the
sa user/pass instead of Windows Authentication.

I can certainly change my ASP.NET apps to use a connection string that uses the
ASP.NET account like:

server=localhost;Int
egrated Security=SSPI;Initia
l Catalog=sqlDb

but, I find that I need to assign basically every database role to the ASP.NET
user for that database, or I get errors on lacking EXECUTE permissions.

If I'm going to the trouble to get rid of the sa connection strings, giving full
security permissions to ASP.NET doesn't seem to be increasing my level of
security for the database, but decreasing it.

I guess I need to find a tech note on setting up proper security permissions for
ASP.NET web database access - as all the books I have don't really cover this.

Thanks, Les Caudle

On Thu, 30 Mar 2006 08:14:27 -0500, "Michael Abraham" < mabraham@decisionarc
.com>
wrote:

>Make sure SQL2005 is configured to accept non-local connections - see the
>Surface Area Configuration utility.
>
>HTH,
>
>Mike
>"Les Caudle" <very@tiredofspam.com> wrote in message
> news:6nnl22hvftmvqma
gp3cke7m3j31uhsnivt@
4ax.com...
>

--
Thanks in advance, Les Caudle

Harry

2006-04-02, 11:23 am

The default installation does not enable TCP or Named Pipes. You need to run
the Surface Configuration Manager and enable access.

You need to create a least-privleged user or add the default one (IUSR_WEB)
to the SQL server, and give that user rights to access/write to that
particular database. Remember that the IUSR_ user is a local user on that
machine.

There is a method of 'passing authentication' through the web server of the
logged in user, but I would make the connection this way after making sure
all of your permissions and settings are good for the web-site's user.

-Harry

"Les Caudle" <very@tiredofspam.com> wrote in message
news:20vn22ttrqks25i
hl3v3fc6il405n9e20m@
4ax.com...
> Mike - SQL2005 is located on the same box as the web server.
>
> I think the problem has to do with security permissions. For some reason,
> the
> sa user/pass seems unable to be used to open the database.
>
> This doesn't make sense to me, as I connected to the Management Studio
> using the
> sa user/pass instead of Windows Authentication.
>
> I can certainly change my ASP.NET apps to use a connection string that
> uses the
> ASP.NET account like:
>
> server=localhost;Int
egrated Security=SSPI;Initia
l Catalog=sqlDb
>
> but, I find that I need to assign basically every database role to the
> ASP.NET
> user for that database, or I get errors on lacking EXECUTE permissions.
>
> If I'm going to the trouble to get rid of the sa connection strings,
> giving full
> security permissions to ASP.NET doesn't seem to be increasing my level of
> security for the database, but decreasing it.
>
> I guess I need to find a tech note on setting up proper security
> permissions for
> ASP.NET web database access - as all the books I have don't really cover
> this.
>
> Thanks, Les Caudle
>
> On Thu, 30 Mar 2006 08:14:27 -0500, "Michael Abraham"
> < mabraham@decisionarc
.com>
> wrote:
>
>
> --
> Thanks in advance, Les Caudle

Sponsored Links