Moving SQL Server database from local to remote machine

Hello,

I have just inherited an ASP project that connects to a SQL Server 2000
database that is currently located on the web server machine. My first task
is to move the SQL Server database to a separate machine. I would also like
to change it to use integrated Windows security instead of mixed mode. (I
have SQL Server experience, but am very new to ASP.)

My first attempts at this resulted in SQL Server error 80004005 ("Login
failed for user 'MyUserName'. Reason: Not associated with a trusted SQL
Server connection." I solved this, but then started getting SQL Server
error 80040e4d (" Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.")

I solved this and got everything working by changing the anonymous user for
the site from the IUSR_MachineName user to a domain user that I created and
granted access rights to the database. (I also made this user a member of
the Domain Guests group in order to restrict the user's rights within the
domain.)

It's all working fine this way, but I'm wondering if I've opened up a
security hole by using a domain user as the anonymous user for the site. Is
this a safe way to go?

Thanks in advance,
--Rob Roberts

Re: Moving SQL Server database from local to remote machine

On Fri, 18 Mar 2005 18:57:54 -0700, "Rob Roberts"
wrote:

>I have just inherited an ASP project that connects to a SQL Server 2000
>database that is currently located on the web server machine. My first task
>is to move the SQL Server database to a separate machine. I would also like
>to change it to use integrated Windows security instead of mixed mode. (I
>have SQL Server experience, but am very new to ASP.)
>
>My first attempts at this resulted in SQL Server error 80004005 ("Login
>failed for user 'MyUserName'. Reason: Not associated with a trusted SQL
>Server connection." I solved this, but then started getting SQL Server
>error 80040e4d (" Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.")
>
>I solved this and got everything working by changing the anonymous user for
>the site from the IUSR_MachineName user to a domain user that I created and
>granted access rights to the database. (I also made this user a member of
>the Domain Guests group in order to restrict the user's rights within the
>domain.)
>
>It's all working fine this way, but I'm wondering if I've opened up a
>security hole by using a domain user as the anonymous user for the site. Is
>this a safe way to go?

It's more a security risk than a local user maybe, but the same
security issues still apply. Plus, it's one of the better ways to
accomplish this.

Jeff

Re: Moving SQL Server database from local to remote machine

Jeff,

>>It's all working fine this way, but I'm wondering if I've opened up a
>>security hole by using a domain user as the anonymous user for the site.
>>Is
>>this a safe way to go?
>
> It's more a security risk than a local user maybe, but the same
> security issues still apply. Plus, it's one of the better ways to
> accomplish this.

Thanks for the info on this.

I've noticed something else about this system that seems like a potential
security problem. The user name and password for accessing the SQL Server
database are stored in clear text in the global.asa file. Is this standard
practice for asp? Isn't there a more secure method for storing the user
name and password? (As I mentioned, I'm very new to asp.)

Thanks,
--Rob Roberts

Re: Moving SQL Server database from local to remote machine

Short of creating a dll, this is pretty much the best you will be able to
do, unless you can get integrated security working ....


Rob Roberts wrote:
> Jeff,
>
>>> It's all working fine this way, but I'm wondering if I've opened up
>>> a security hole by using a domain user as the anonymous user for
>>> the site. Is
>>> this a safe way to go?
>>
>> It's more a security risk than a local user maybe, but the same
>> security issues still apply. Plus, it's one of the better ways to
>> accomplish this.
>
> Thanks for the info on this.
>
> I've noticed something else about this system that seems like a
> potential security problem. The user name and password for accessing
> the SQL Server database are stored in clear text in the global.asa
> file. Is this standard practice for asp? Isn't there a more secure
> method for storing the user name and password? (As I mentioned, I'm
> very new to asp.)
>
> Thanks,
> --Rob Roberts

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Moving SQL Server database from local to remote machine

On Mon, 21 Mar 2005 09:57:02 -0700, "Rob Roberts"
wrote:

>Jeff,
>
>>>It's all working fine this way, but I'm wondering if I've opened up a
>>>security hole by using a domain user as the anonymous user for the site.
>>>Is
>>>this a safe way to go?
>>
>> It's more a security risk than a local user maybe, but the same
>> security issues still apply. Plus, it's one of the better ways to
>> accomplish this.
>
>Thanks for the info on this.
>
>I've noticed something else about this system that seems like a potential
>security problem. The user name and password for accessing the SQL Server
>database are stored in clear text in the global.asa file. Is this standard
>practice for asp? Isn't there a more secure method for storing the user
>name and password? (As I mentioned, I'm very new to asp.)

If you use Windows integrated security you don't have to store a
username/password. But the fact that it's in the global.asa isn't
that big a risk since you can't download or access the global.asa
directly from an internet URL.

Jeff

Re: Moving SQL Server database from local to remote machine

Jeff,

> If you use Windows integrated security you don't have to
> store a username/password.

Thanks very much! I hadn't thought of that, but now that you've mentioned
it, it makes sense. I tried simply removing the user name and password from
the connection string in global.asa, and it's working fine.

Thanks again!
--Rob Roberts

Re: Moving SQL Server database from local to remote machine

Bob,

> Short of creating a dll, this is pretty much the best you will be
> able to do, unless you can get integrated security working ....

I have integrated security working, and after reading Jeff's message I tried
removing the user name and password from the global.asa file, and it seems
to be working fine. So this seems like a good solution.

Thanks,
--Rob Roberts