Impact of Changing Authentication Mode

Hi,
Our application using an SQL 2000 database, which is currently using Windows
Authentication. For some reason, we need to change the access mode to mixed
mode. Could someone please throw some light on the impact of this change.
Thanks in advance
SuhasOne extra door to guard?
Not sure what you mean by impact. From a performance or resource overhead
perspective? Nothing there. From a security perspective, you now need to
figure out how to manage the SQL Server logins to ensure they follow your
password policies (complexity, length, expiration, re-use, etc...). Not much
help built into SQL Server 2000 but in 2005, life gets a LOT better.
joe.
"Suhas Vengilat" <SuhasVengilat@.discussions.microsoft.com> wrote in message
news:34F882D9-448F-4EDC-A62C-BB87CCAF9307@.microsoft.com...
> Hi,
> Our application using an SQL 2000 database, which is currently using
> Windows
> Authentication. For some reason, we need to change the access mode to
> mixed
> mode. Could someone please throw some light on the impact of this change.
> Thanks in advance
> Suhas|||With either SQL Server 2000 or SQL Server 2005, the process is virtually
identical as follows:
Run SQL Enterprise Manager (or Start > Run > SQLwb.exe if using 2005)
Connect to the server
Launch Server Properties dialog
Switch to the Security tab
Select the Authentication option (Mixed or Windows only) > OK
Dismiss the "restart the server to implement changes" message box
"Restart" the MSSQLServer service (Start > Run > Compmgmt.msc >
services.msc)
Regards,
Keith
This posting is provided "as is" without inferring rights or warranties.
"Joe Yong" <NO_jyong_SPAM@.scalabilityexperts.com> wrote in message
news:OVSeLmxvFHA.1032@.TK2MSFTNGP12.phx.gbl...
> One extra door to guard?
> Not sure what you mean by impact. From a performance or resource overhead
> perspective? Nothing there. From a security perspective, you now need to
> figure out how to manage the SQL Server logins to ensure they follow your
> password policies (complexity, length, expiration, re-use, etc...). Not
> much help built into SQL Server 2000 but in 2005, life gets a LOT better.
>
> joe.
>
> "Suhas Vengilat" <SuhasVengilat@.discussions.microsoft.com> wrote in
> message news:34F882D9-448F-4EDC-A62C-BB87CCAF9307@.microsoft.com...
>

Impact of AD Migration on Windows authenticated SQL user account

We have SQL applications in which some of the SQL user
accounts are integrated with Windows authentication.
We would like to find out whether any of you have
migrated any SQL based applications of which the SQL user
accounts were configured to make use of Windows
authentication instead of SQL authentication. Will the
Windows accounts in SQL be automatically translated to
the new AD account (with all necessary SQL
rights/permissions) or do we have to manually recreate &
reconfigure all Windows accounts in SQL and its
rights/permissions?
Appreciate some feedback. Thanks.
.Hi
If you are using the AD migration tool then you should keep the same SIDs
and therefore there should not be a problem.
If you do change SIDS then the amount of rework would be reduced if you used
NT groups when granting premissions rather than individual users.
John
"Teo Chee Yang" <anonymous@.discussions.microsoft.com> wrote in message
news:c11b01c40893$25655870$a301280a@.phx.gbl...
> We have SQL applications in which some of the SQL user
> accounts are integrated with Windows authentication.
> We would like to find out whether any of you have
> migrated any SQL based applications of which the SQL user
> accounts were configured to make use of Windows
> authentication instead of SQL authentication. Will the
> Windows accounts in SQL be automatically translated to
> the new AD account (with all necessary SQL
> rights/permissions) or do we have to manually recreate &
> reconfigure all Windows accounts in SQL and its
> rights/permissions?
> Appreciate some feedback. Thanks.
> .
>|||Personally I doubt that ADMT does translate any SQL
permissions and rights assigned to Windows-authenticated
SQL user accounts.
Besides, I realized that from the Security, Logins folder
that once the logins are defined as <NT4
domain>\<username>, we cant changed it, even before the
migration of the SQL server computer account.
Any more feedback?

>--Original Message--
>Hi
>If you are using the AD migration tool then you should
keep the same SIDs
>and therefore there should not be a problem.
>If you do change SIDS then the amount of rework would be
reduced if you used
>NT groups when granting premissions rather than
individual users.
>John
>"Teo Chee Yang" <anonymous@.discussions.microsoft.com>
wrote in message
>news:c11b01c40893$25655870$a301280a@.phx.gbl...
user
&
>
>.
>|||Hi
This sounds like you are changing the domain as well during the migration.
In which case look at:
http://support.microsoft.com/defaul...kb;EN-US;240872
I believe in the documentation that SIDs are retained by ADMT, if it did not
all sorts of other privilege related problems would occur.
John
"Teo Chee Yang" <anonymous@.discussions.microsoft.com> wrote in message
news:c64d01c40915$7d155610$a401280a@.phx.gbl...
> Personally I doubt that ADMT does translate any SQL
> permissions and rights assigned to Windows-authenticated
> SQL user accounts.
> Besides, I realized that from the Security, Logins folder
> that once the logins are defined as <NT4
> domain>\<username>, we cant changed it, even before the
> migration of the SQL server computer account.
> Any more feedback?
>
> keep the same SIDs
> reduced if you used
> individual users.
> wrote in message
> user
> &

Im getting a Login failed... error when trying to open my database from my default.aspx.vb

Good morning,

When I start my browser and try to open my (SQL Server 2000) database, using the 'Integrated Windows Authentication' method, I get the following error msg:
[SqlException: Login failed for user 'MYDOMAIN\ASPNET'.].

Where DOMAIN = MYDOMAIN
and USER = ASPNET
I have made this user known to the SQL Server 2000...

I opened my browser and executed the following function, which is in my default.aspx.vb file:

Public Function checkuser(ByVal userName As String, ByVal password As String) As Boolean
Dim strConnection As String = _
"Data Source=localhost;Initial Catalog=dbo; Integrated Security=True"
Dim sqlconQCards As SqlConnection = New SqlConnection(strConnection)
sqlconQCards.Open()
End Function

Rather than opening the database, the error message, shown above, is displayed.

I'd appreciate any help.

Thanks,
PatDid you add the user in the database itself ?!

Rudy|||Hi Rudy,

I finally got it to work by changing to 'SQL Server Authentication'. It requres that the user/pswd be supplied.

The 'Integrated Windows Authentication', which does not require the user/pswd, is not working for me...This is the Microsoft recommended method...

I am using Microsoft SQL Server 2000...

Thanks,
Pat

IIS6, VPN, SQL Server 2000

If we have a SQL Server 2000 database on our internal LAN and an
external IIS6 Server with ASP.NET pages using SQL Authentication talking
to the SQL Server through the Internet "cloud" via VPN/Firewall Gateway
devices on either end of the connection (with port 1433 enabled), would
the traffic over the VPN be transparent to SQL Server or do we need to
configure SQL Server to handle it? We are running Windows 2003 server.
Thanks
TimoSQL-- IIS --VPN--Internet--VPN
I'm asuming this is your configuration..
So if your configuration is like this, then once the VPN tunnel is made,
then whatever protocols you're using will be sent across the VPN.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin,
Thanks for the reply. Actually, we will have two IIS Servers, IIS#1 on
our internal LAN used for intranet apps, IIS#2 external for internet
apps visible to the public. The configuration would be like this:
{LAN: IIS#1--SQL } --VPN--{tunnel}--VPN--IIS#2--{Internet}
If I understand you correctly, when SQL gets the request from the IIS#
2, the traffic will look to SQL no different from any other traffic over
port 1433, even though it has come through the tunnel.
Apart from making sure the VPN/Firewall has port 1433 open, is there
anything else required of the VPN to support traffic between SQL and
IIS#2?
Timo
In article <X3QoXmmJEHA.3088@.cpmsftngxa10.phx.gbl>,
kevmc@.online.microsoft.com says...
> SQL-- IIS --VPN--Internet--VPN
> I'm asuming this is your configuration..
> So if your configuration is like this, then once the VPN tunnel is made,
> then whatever protocols you're using will be sent across the VPN.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.|||From what I can see your analysis is correct.
Cheers
Ken
"Timo" <t@.anonymous.com> wrote in message
news:MPG.1aeed52797260409896be@.msnews.microsoft.com...
: Kevin,
: Thanks for the reply. Actually, we will have two IIS Servers, IIS#1 on
: our internal LAN used for intranet apps, IIS#2 external for internet
: apps visible to the public. The configuration would be like this:
:
: {LAN: IIS#1--SQL } --VPN--{tunnel}--VPN--IIS#2--{Internet}
:
: If I understand you correctly, when SQL gets the request from the IIS#
: 2, the traffic will look to SQL no different from any other traffic over
: port 1433, even though it has come through the tunnel.
:
: Apart from making sure the VPN/Firewall has port 1433 open, is there
: anything else required of the VPN to support traffic between SQL and
: IIS#2?
: Timo
:
: In article <X3QoXmmJEHA.3088@.cpmsftngxa10.phx.gbl>,
: kevmc@.online.microsoft.com says...
: >
: > SQL-- IIS --VPN--Internet--VPN
: >
: > I'm asuming this is your configuration..
: > So if your configuration is like this, then once the VPN tunnel is made,
: > then whatever protocols you're using will be sent across the VPN.
: >
: >
: >
: > Thanks,
: >
: > Kevin McDonnell
: > Microsoft Corporation
: >
: > This posting is provided AS IS with no warranties, and confers no
rights.|||previous post;
"If I understand you correctly, when SQL gets the request from the IIS#
2, the traffic will look to SQL no different from any other traffic over
port 1433, even though it has come through the tunnel.
"
Correct.
"Apart from making sure the VPN/Firewall has port 1433 open, is there
anything else required of the VPN to support traffic between SQL and
IIS#2?"
No.
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Another question has arisen and we don't have a system in place
yet to determine the answer by trial and error.
When IIS6 on somedomain.com communicates with SQLServer2000 on
someotherdomain.net via a VPN, should the Connection String
specify the SQLServer by name with Standard Security:
"Data Source=OurSQLServer;Initial Catalog=pubs;User
Id=sa;Password=foo;"
Or should the Connection String use the IP address mode:
Data Source=xxx.xxx.xxx.xxx,1433;Network Library=DBMSSOCN;Initial
Catalog=pubs;User ID=sa;Password=foo;"
If the Connection String should use the IP address mode with
DBMSSOCN, should it specify the IP address of the firewall (and
let the firewall translate 1433 traffic to the SQLServer by NAT)
or should it specify the IP address of the actual SQL Server?
Thanks!
Timo|||So, if you're on the outside of the firewall, you're client machine has no
knowledge of how to resolve a netbios name on the
inside of the firewall. You should be able to use the IP address of the
firewall, and allow it to NAT to the internal server.
Otherwise, if you want to use the fqdn name, add an entry to your host
file, or lmhost file if you want to use the netbios name.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

IIS6 connect to sql server on another machine w/windows authentication

Can someone point me at an article that tells how to allow theASP.net worker process to connect, via windows authentication, to aremote sql server instance ?

Start with:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/PAGHT000008.asp

The remote server access is in the connection string, specifying server and instance.

Jeff

|||

Also make sure remote connections enabled on the remote SQL instnace. Check this KB:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

IIS server set up for .net app

Hi,
I use windows authentication for .net app to connection to sql 2k server.
I got the following error --(Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection)
Connection string -- server=sql serv1;database=mydb;trusted_connection
= true
appserver = app1
Any ideas?
The idea is that the windows user account used to run your application
(Window Form app or ASP.NET app?) does not have a mapped SQL Server login
created in the SQL Server, thus cannot log into SQL Server.
You need to create a SQL Server login that mapps to that windows user
account or a Windows user security group, which includes that user account
as member. Then you need to add this SQL Server login to target database as
user, then assign appropriate permissions.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>
|||Ensure that the Windows Account that you work your app under is added to the
domain and it has a valid Login in your SQL Server.
If the Windows user account is not in your domain and you use Trusted
Connections then you get this error.
Ekrem nsoy
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>

IIS server set up for .net app

Hi,
I use windows authentication for .net app to connection to sql 2k server.
I got the following error --(Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection)
Connection string -- server=sql serv1;database=mydb;trusted_connection
= true
appserver = app1
Any ideas?The idea is that the windows user account used to run your application
(Window Form app or ASP.NET app?) does not have a mapped SQL Server login
created in the SQL Server, thus cannot log into SQL Server.
You need to create a SQL Server login that mapps to that windows user
account or a Windows user security group, which includes that user account
as member. Then you need to add this SQL Server login to target database as
user, then assign appropriate permissions.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>|||Ensure that the Windows Account that you work your app under is added to the
domain and it has a valid Login in your SQL Server.
If the Windows user account is not in your domain and you use Trusted
Connections then you get this error.
Ekrem nsoy
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>

IIS server set up for .net app

Hi,
I use windows authentication for .net app to connection to sql 2k server.
I got the following error --(Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection)
Connection string -- server=sql serv1;database=mydb;trusted_connection
= true
appserver = app1
Any ideas?The idea is that the windows user account used to run your application
(Window Form app or ASP.NET app?) does not have a mapped SQL Server login
created in the SQL Server, thus cannot log into SQL Server.
You need to create a SQL Server login that mapps to that windows user
account or a Windows user security group, which includes that user account
as member. Then you need to add this SQL Server login to target database as
user, then assign appropriate permissions.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>|||Ensure that the Windows Account that you work your app under is added to the
domain and it has a valid Login in your SQL Server.
If the Windows user account is not in your domain and you use Trusted
Connections then you get this error.
--
Ekrem Önsoy
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>

IIS detection on Vista: Where's my Windows Authentication option ?

I am trying to install RS on Vista. I notice that the list of IIS features to turn on, listed in the KB article and blog entries, mentions a "Windows Authentication" IIS feature to check under Security. I don't have that (although I have one labeled "Basic Authentication") -- I'm on a "workgroup" computer at home.

Does this mean that you simply can't install RS on Vista unless you're joined to a domain? Thanks,

-Doug

You an use Basic Authentication. Windows Auth is more secure and the suggested auth type. You do not need to be joined to a domain to install RS.|||

The issue is not which options are supported by RS, but which options allow SQL Setup to detect the presence of IIS 7 successfully, as a pre-condition to installing RS.

I have since discovered my answer, though it's an annoying and ironic one: IIS 7 does not support Windows Authentication in Windows Home Premium Edition. Therefore RS cannot be installed. Windows Authentication is one of the requirements for SQL Setup to detect IIS successfully.

What's in IIS 7 by Vista edition: http://www.iis.net/default.aspx?tabid=2&subtabid=25&i=1100

What SQL Setup needs to detect IIS: http://support.microsoft.com/kb/920201/en-us

Rationale: "The IIS 7 role on the Windows Vista Home Premium Edition is to support the needs of the casual or hobbyist web developers; as such the majority of the IIS 7 Web Server features required for web site development are available. Features not normally associated with hobbyist web development such as FTP server, advanced Web authentication and authorization, and remote administration are not available on the Vista Home Premium Edition."

-Doug

IIS detection on Vista: Where's my Windows Authentication option ?

I am trying to install RS on Vista. I notice that the list of IIS features to turn on, listed in the KB article and blog entries, mentions a "Windows Authentication" IIS feature to check under Security. I don't have that (although I have one labeled "Basic Authentication") -- I'm on a "workgroup" computer at home.

Does this mean that you simply can't install RS on Vista unless you're joined to a domain? Thanks,

-Doug

You an use Basic Authentication. Windows Auth is more secure and the suggested auth type. You do not need to be joined to a domain to install RS.|||

The issue is not which options are supported by RS, but which options allow SQL Setup to detect the presence of IIS 7 successfully, as a pre-condition to installing RS.

I have since discovered my answer, though it's an annoying and ironic one: IIS 7 does not support Windows Authentication in Windows Home Premium Edition. Therefore RS cannot be installed. Windows Authentication is one of the requirements for SQL Setup to detect IIS successfully.

What's in IIS 7 by Vista edition: http://www.iis.net/default.aspx?tabid=2&subtabid=25&i=1100

What SQL Setup needs to detect IIS: http://support.microsoft.com/kb/920201/en-us

Rationale: "The IIS 7 role on the Windows Vista Home Premium Edition is to support the needs of the casual or hobbyist web developers; as such the majority of the IIS 7 Web Server features required for web site development are available. Features not normally associated with hobbyist web development such as FTP server, advanced Web authentication and authorization, and remote administration are not available on the Vista Home Premium Edition."

-Doug

IIS Basic authentication problem - 401 Unauthorized error

Hello all,
I have users attempting to view reports (created in Reporting Services 2000)
over the web and they're receiving a "401 - Unauthorized" error due to
invalid credentials. Here is the VB.NET code that's being used to
authenticate the user:
Dim strURL as String = "http://mydomain.mywebsite.com/ReportServer?MyFolder/MyReport?arg1=1&arg2=2&rs:Command=Render&rs:Format=PDF"
Dim ReportWebRequest As HttpWebRequest = CType(WebRequest.Create(strReportURL), HttpWebRequest)
ReportWebRequest.Timeout = 1000000
ReportWebRequest.MaximumAutomaticRedirections = 50
ReportWebRequest.Headers.Add("Authorization", "Basic " +
Convert.ToBase64String(Encoding.ASCII.GetBytes("userid:password")))
ReportWebRequest.PreAuthenticate = True
Dim ReportWebResponse As HttpWebResponse = CType(ReportWebRequest.GetResponse(), HttpWebResponse)
We would prefer not to turn on anonymous access. We are running IIS vers.6
on a Win 2003 Server. Any ideas?
Thanks in advance,
BruceBruce,
Try replacing the following lines of code...
ReportWebRequest.Headers.Add("Authorization", "Basic " +
Convert.ToBase64String(Encoding.ASCII.GetBytes("userid:password")))
ReportWebRequest.PreAuthenticate = True
...with these lines:
Dim cCache = New CredentialCache
cCache.Add(New Uri(strReportURL), "Basic", New NetworkCredential("userid",
"password", "domain"))
ReportWebRequest.Credentials = cCache
Hope this helps,
Steve
"Bruce A" wrote:
> Hello all,
> I have users attempting to view reports (created in Reporting Services 2000)
> over the web and they're receiving a "401 - Unauthorized" error due to
> invalid credentials. Here is the VB.NET code that's being used to
> authenticate the user:
> Dim strURL as String => "http://mydomain.mywebsite.com/ReportServer?MyFolder/MyReport?arg1=1&arg2=2&rs:Command=Render&rs:Format=PDF"
> Dim ReportWebRequest As HttpWebRequest => CType(WebRequest.Create(strReportURL), HttpWebRequest)
> ReportWebRequest.Timeout = 1000000
> ReportWebRequest.MaximumAutomaticRedirections = 50
> ReportWebRequest.Headers.Add("Authorization", "Basic " +
> Convert.ToBase64String(Encoding.ASCII.GetBytes("userid:password")))
> ReportWebRequest.PreAuthenticate = True
> Dim ReportWebResponse As HttpWebResponse => CType(ReportWebRequest.GetResponse(), HttpWebResponse)
> We would prefer not to turn on anonymous access. We are running IIS vers.6
> on a Win 2003 Server. Any ideas?
> Thanks in advance,
> Bruce

IIS Authentication for RDA not working

My Code:

rda.InternetLogin = "domain\username"

rda.InternetPassword = "password"

rda.SubmitSql("SELECT * FROM Table", rdaOleDbConnectString)

I have a client trying to use my application through SSL.

https://DOMAINCONTROLLER/sqlmobile/sqlcesa30.dll

they do not have certificiates setup...and it isn't working at all.

Any help?

You cannot use SSL without a public certificate installed on the server, and that certificate's root must be present on the device.

IIS and Sql report server

I use a hosting company that offers SQL reporting tools. However, they have basic authentication setup and will not remove it. So, when I use reportviewer, it prompts for a iis username and password. I only have one user account for the reporting tools and they do not create anymore. I do not want to give other people this username and password. So, with that being said. Is there anyway to auto populate the username and password field in asp.net? There is a way to do it in javascript. Like so:

<html>
<head>
<title>Frames</title>
<script language="javascript">
function populate()
{
var p = parent.frames["body"];
p.frm.elements["txtUsername"].value = "username";
p.frm.elements["txtPassword"].value = "password";
}
</script>
</head>
<frameset>
<frame name="body" src="http://pics.10026.com/?src=loginpage.aspx" onload="populate()">
</frameset>
</html>

However, if you did a view source, you'd have the username and password information. Is there any way to achieve this effect whether it be auto population through a frameset or some other method. My site and the sql reporting tools are on TWO different servers and have two different username and passwords. Someone had mentioned doing impersonation but it was not successful. Any ideas?

how bout u store ur username and name in the webconfig file.. and u call the report set the credentials...

so that u dont have to display the username and password to the user.

IIS 6 and Windows Authentication to SQL Server 2000

I am running a Windows Server 2003 machine as my web server. I would
like to use Windows authentication for connections to my SQL Server
2000 instance on a Windows 2000 server. I've read where mirroring the
ASPNET account and password on the web server and SQL server would
work. However, with IIS 6, ASP.NET runs under the 'NT
AUTHORITY\NETWORK SERVICE' account. Should I change the password of
the 'NT AUTHORITY\NETWORK SERVICE' account to something I know, and
create a mirrored 'NETWORK SERVICE' account on my SQL server? Or,
should I create another user like 'MY_WEB_USER' and mirror that on both
machines?
In short, how do I get Windows authentication to work between a Windows
Server 2003 web server and a Windows 2000 SQL server?
Thanks!
If you are truly using Windows Authentication, ie a user has an account on
the domain as well as SQL Server, you will do the following:
1. Ensure the user cannot sign in as anonymous
2. Add their account to a group that has SQL rights
You may mean "bastardized windows authentication", meaning SQL Server uses
WIndows Authentication, but you are using anon accounts in IIS. If you go
this route, you are advised to impersonate an account rather than give a
local account rights on another box. One way to easily do this is to place
the assembly in COM+ and declaratively assign a domain account to the
application.

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"mcollier" wrote:

> I am running a Windows Server 2003 machine as my web server. I would
> like to use Windows authentication for connections to my SQL Server
> 2000 instance on a Windows 2000 server. I've read where mirroring the
> ASPNET account and password on the web server and SQL server would
> work. However, with IIS 6, ASP.NET runs under the 'NT
> AUTHORITY\NETWORK SERVICE' account. Should I change the password of
> the 'NT AUTHORITY\NETWORK SERVICE' account to something I know, and
> create a mirrored 'NETWORK SERVICE' account on my SQL server? Or,
> should I create another user like 'MY_WEB_USER' and mirror that on both
> machines?
> In short, how do I get Windows authentication to work between a Windows
> Server 2003 web server and a Windows 2000 SQL server?
> Thanks!
>
|||Hi,
Are both the servers in the same domain?
If so create a domain user account and switch the identity of the ASP.NET
worker process to this acccount. You can do this by creating a new
application pool in IIS6.
Now configure this acccount as a login for sql server.
Don't forget to add this user to the IIS_WPG group on your Win2K3 machine.
HTH
Vikram Vamshi
Eclipsys Corporation
"mcollier" <michaelscollier@.gmail.com> wrote in message
news:1109016547.061210.221620@.f14g2000cwb.googlegr oups.com...
>I am running a Windows Server 2003 machine as my web server. I would
> like to use Windows authentication for connections to my SQL Server
> 2000 instance on a Windows 2000 server. I've read where mirroring the
> ASPNET account and password on the web server and SQL server would
> work. However, with IIS 6, ASP.NET runs under the 'NT
> AUTHORITY\NETWORK SERVICE' account. Should I change the password of
> the 'NT AUTHORITY\NETWORK SERVICE' account to something I know, and
> create a mirrored 'NETWORK SERVICE' account on my SQL server? Or,
> should I create another user like 'MY_WEB_USER' and mirror that on both
> machines?
> In short, how do I get Windows authentication to work between a Windows
> Server 2003 web server and a Windows 2000 SQL server?
> Thanks!
>
|||Both servers are not in the same domain. I've done this before when I
had two Windows 2000 servers. Having one Windows 2003 and one Windows
2000 server seems to be somewhat more confusing.
|||What you did for WIndows 2000 should still work for Win2K3.
Create a user on both the machines with the same username and password.
Then configure the ASP.NET worker process to run under this user account on
win2k3 machine
and configure sql on win2k machine to accept this user as a valid login.
As long as the username/password are same on both the machines this should
work.
hth
Vikram Vamshi
Eclipsys Corporation
"mcollier" <michaelscollier@.gmail.com> wrote in message
news:1109041768.768522.120100@.f14g2000cwb.googlegr oups.com...
> Both servers are not in the same domain. I've done this before when I
> had two Windows 2000 servers. Having one Windows 2003 and one Windows
> 2000 server seems to be somewhat more confusing.
>
|||Ok, I think I see where you're going with this. I was thinking I could
use the ASPNET or NETWORK SERVICE account for both servers. But, that
doesn't appear to be the case. What you're saying is that I could
create a user on both servers, for example MY_WEB_USER. Then, set the
ASP.NET worker process to run as this account. I would also need to
give that user the correct permissions (similar to NETWORK SERVICE
probably). Sound about right?
|||Yep,
That is what I had in mind.
Let us know how it goes.
Thanks
Vikram Vamshi
Eclipsys Corporation
"mcollier" <michaelscollier@.gmail.com> wrote in message
news:1109125129.410024.221630@.f14g2000cwb.googlegr oups.com...
> Ok, I think I see where you're going with this. I was thinking I could
> use the ASPNET or NETWORK SERVICE account for both servers. But, that
> doesn't appear to be the case. What you're saying is that I could
> create a user on both servers, for example MY_WEB_USER. Then, set the
> ASP.NET worker process to run as this account. I would also need to
> give that user the correct permissions (similar to NETWORK SERVICE
> probably). Sound about right?
>

IIS 6 and Windows Authentication to SQL Server 2000

I am running a Windows Server 2003 machine as my web server. I would
like to use Windows authentication for connections to my SQL Server
2000 instance on a Windows 2000 server. I've read where mirroring the
ASPNET account and password on the web server and SQL server would
work. However, with IIS 6, ASP.NET runs under the 'NT
AUTHORITY\NETWORK SERVICE' account. Should I change the password of
the 'NT AUTHORITY\NETWORK SERVICE' account to something I know, and
create a mirrored 'NETWORK SERVICE' account on my SQL server? Or,
should I create another user like 'MY_WEB_USER' and mirror that on both
machines?
In short, how do I get Windows authentication to work between a Windows
Server 2003 web server and a Windows 2000 SQL server?
Thanks!If you are truly using Windows Authentication, ie a user has an account on
the domain as well as SQL Server, you will do the following:
1. Ensure the user cannot sign in as anonymous
2. Add their account to a group that has SQL rights
You may mean "bastardized windows authentication", meaning SQL Server uses
WIndows Authentication, but you are using anon accounts in IIS. If you go
this route, you are advised to impersonate an account rather than give a
local account rights on another box. One way to easily do this is to place
the assembly in COM+ and declaratively assign a domain account to the
application.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"mcollier" wrote:

> I am running a Windows Server 2003 machine as my web server. I would
> like to use Windows authentication for connections to my SQL Server
> 2000 instance on a Windows 2000 server. I've read where mirroring the
> ASPNET account and password on the web server and SQL server would
> work. However, with IIS 6, ASP.NET runs under the 'NT
> AUTHORITY\NETWORK SERVICE' account. Should I change the password of
> the 'NT AUTHORITY\NETWORK SERVICE' account to something I know, and
> create a mirrored 'NETWORK SERVICE' account on my SQL server? Or,
> should I create another user like 'MY_WEB_USER' and mirror that on both
> machines?
> In short, how do I get Windows authentication to work between a Windows
> Server 2003 web server and a Windows 2000 SQL server?
> Thanks!
>|||Hi,
Are both the servers in the same domain?
If so create a domain user account and switch the identity of the ASP.NET
worker process to this acccount. You can do this by creating a new
application pool in IIS6.
Now configure this acccount as a login for sql server.
Don't forget to add this user to the IIS_WPG group on your Win2K3 machine.
HTH
--
Vikram Vamshi
Eclipsys Corporation
"mcollier" <michaelscollier@.gmail.com> wrote in message
news:1109016547.061210.221620@.f14g2000cwb.googlegroups.com...
>I am running a Windows Server 2003 machine as my web server. I would
> like to use Windows authentication for connections to my SQL Server
> 2000 instance on a Windows 2000 server. I've read where mirroring the
> ASPNET account and password on the web server and SQL server would
> work. However, with IIS 6, ASP.NET runs under the 'NT
> AUTHORITY\NETWORK SERVICE' account. Should I change the password of
> the 'NT AUTHORITY\NETWORK SERVICE' account to something I know, and
> create a mirrored 'NETWORK SERVICE' account on my SQL server? Or,
> should I create another user like 'MY_WEB_USER' and mirror that on both
> machines?
> In short, how do I get Windows authentication to work between a Windows
> Server 2003 web server and a Windows 2000 SQL server?
> Thanks!
>|||Both servers are not in the same domain. I've done this before when I
had two Windows 2000 servers. Having one Windows 2003 and one Windows
2000 server seems to be somewhat more confusing.|||What you did for Windows 2000 should still work for Win2K3.
Create a user on both the machines with the same username and password.
Then configure the ASP.NET worker process to run under this user account on
win2k3 machine
and configure sql on win2k machine to accept this user as a valid login.
As long as the username/password are same on both the machines this should
work.
hth
Vikram Vamshi
Eclipsys Corporation
"mcollier" <michaelscollier@.gmail.com> wrote in message
news:1109041768.768522.120100@.f14g2000cwb.googlegroups.com...
> Both servers are not in the same domain. I've done this before when I
> had two Windows 2000 servers. Having one Windows 2003 and one Windows
> 2000 server seems to be somewhat more confusing.
>|||Ok, I think I see where you're going with this. I was thinking I could
use the ASPNET or NETWORK SERVICE account for both servers. But, that
doesn't appear to be the case. What you're saying is that I could
create a user on both servers, for example MY_WEB_USER. Then, set the
ASP.NET worker process to run as this account. I would also need to
give that user the correct permissions (similar to NETWORK SERVICE
probably). Sound about right?|||Yep,
That is what I had in mind.
Let us know how it goes.
Thanks
--
Vikram Vamshi
Eclipsys Corporation
"mcollier" <michaelscollier@.gmail.com> wrote in message
news:1109125129.410024.221630@.f14g2000cwb.googlegroups.com...
> Ok, I think I see where you're going with this. I was thinking I could
> use the ASPNET or NETWORK SERVICE account for both servers. But, that
> doesn't appear to be the case. What you're saying is that I could
> create a user on both servers, for example MY_WEB_USER. Then, set the
> ASP.NET worker process to run as this account. I would also need to
> give that user the correct permissions (similar to NETWORK SERVICE
> probably). Sound about right?
>

IIS (ASP) -> SQLServer Authentication Issue

I have seen numerous pages and forum e-mails about this issue on the
Internet and would like to know if someone can tell me if it is possible to
do the following:
- IIS 6 machine serving ASP pages, integrated authentication, machine
trusted for delegation in the domain
- SQLServer 2000 machine with mixed authentication (same domain as IIS 6
machine)
- I want the ASP pages to access the SQLServer machine using the
authentication credentials provided to IIS as part of integrated
authentication
As far as I can determine, it should be possible as covered in this article
http://support.microsoft.com/kb/319723/en-us. However, I'm currently
getting the error:
Microsoft OLE DB Provider for SQL Server error '80004005'
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
My problem is that I've tried to make this work in our environment in the
past without success. A couple of months ago, it suddenly started working,
and then recently (a day ago) stopped again. The system administrators say
no changes were made that would cause it to stop working.
When researching this issue again on the Internet I did not find many people
trying to do what I wanted to do. Understandably, most people are
interested in solutions where the users accessing the web pages are not
domain users that have access to the SQLServer database - but in my
situation that's what I have. The only solutions I saw offered to this
solution were to turn on Basic Authentication (I know this will work, but do
not want to do this as it negatively impacts the user experience and do not
want passwords crossing the network in the clear) or to setup some other
domain account (I don't want to do this because I want to be using the user
accounts) that IIS would use to access SQLServer. No mention was made of
the kerberos/delegation solution that I identified above.
Is it possible to do what the article describes above? Is there any reason
it should not work? Has anyone had success with the above article?
Thanks, TylerHello Tyler,
I understand that you'd like to use IIS Intergration authentication in the
intranet, and ASP "impersonates" authencitaed users to access SQL Server on
a different machine in the same domain. However, you receive
'80004005'error. If I'm off-base, please let me know.
As you know, only kerberos authentication allows double-hops from clients
via IIS to SQL Server. Windows NT Challenge/Response does not support
double-hop impersonations (meaning that once passed to the IIS server, the
same credentials cannot be passed to a back-end server for authentication,
for example, when IIS uses Windows NT Challenge/Response, it cannot then
authenticate the user against a SQL Server database on another computer by
using SQL Integrated security). Please the following article for details:
How IIS authenticates browser clients
http://support.microsoft.com/defaul...kb;en-us;264921
Per your question, it is possible that IIS uses kerberos authentication and
impersontes the domain user to access backend SQL Server. This is also what
described in 319723. Though some old KBs mention that ASP/IIS must use
Basic Authentication if SQL Server is on a diferent machine, it is not true
any more.
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/defaul...kb;en-us;247931
PRB: "Client Unable to Establish Connection" Error Message When Connecting
from ASP to SQL Server
http://support.microsoft.com/defaul...kb;en-us;253500
You may want to start from 319723 which I think you have done. Also, you
must use TCPIP for Kerberos of SQL Server. Named pipes do not support
Kerberos delegation. Named pipes use NTLM only.
From the error, "Login failed for user '(null)'", it seems the your SQL
Server doesn't have an SPN registered properly. I suggest that you manually
add the SPN
setspn -A MSSQLSvc/<FQDN of SQL Server>:<port> <Sql Service Account>
setspn -A MSSQLSvc/<server hostname>:<port> <Sql Service Account>
For example:
setspn -A MSSQLSvc/sql1.test.com:1433 testdomain\sqlservice
setspn -A MSSQLSvc/sql1:1433 testdomain\sqlservice
You may want to use the following command to see the SPN registred to the
sql server startup account.
setspn -L <domain name>\<sql server startup account>
How to troubleshoot the "Cannot generate SSPI context" error message
http://support.microsoft.com/kb/811889
If the issue persists, you may want to enable security event logging and
Kerberos event logging on both SQL Server and IIS Server to see more
details:
How to troubleshoot Kerberos-related issues in IIS
http://support.microsoft.com/kb/326985/
262177 How to enable Kerberos event logging
http://support.microsoft.com/kb/262177
If the Internet Explorer client is set to use a proxy server, you must
click to select the Bypass Proxy Server for local addresses check box. To
verify that the Internet Explorer client is set to use a proxy server, this
follow these steps: a. Start Internet Explorer.
b. On the Tools menu, click Internet Options, and then click the
Connections tab.
c. Click LAN Settings. Under Proxy server, verify that the Bypass proxy
server for local address check box is checked.
Also, if Anonymous authentication is enabled, IIS will always try to
authenticate by using it first, even if other methods are enabled. If
Anonymous authentication, Integrated Windows authentication, and Basic
authentication are all selected, Integrated Windows authentication takes
precedence over Basic authentication, after Anonymous authentication.
More related information
Troubleshooting Kerberos Delegation
http://www.microsoft.com/downloads/...f94f-e28a-4726-
bffe-2f64ae2f59a2&displaylang=en
Download the Kerbtray and verify Kerberos tickets from any of the
associated computers that are being used.
http://www.microsoft.com/windows200...isting/kerbtray
-o.asp
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||You have described exactly what we want to see work. I will let you know if
I'm still not able to get it working with all of the information you've
problem.
Thank you very much,
Tyler|||Hello Tyler,
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hello Tyler,
I'm still interested in this issue. Do you have any update of the test? If
you have any comments or questions, please feel free to let's know. We look
forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Peter,
The information and links you provided were quite helpful. I believe it was
an SPN problem, but unfortunately I cannot say for certain. I had to hand
the information and request off to another member of our IT Support team to
resolve because I am not a domain administrator and didn't have the
necessary access rights to perform all of the diagnosis. I am glad to
report though that the issue has been resolved and things are working as we
expect.
Thanks very much for your assistance with this issue - your help was
invaluable
Tyler
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:pUb3csfNHHA.2300@.TK2MSFTNGHUB02.phx.gbl...
> Hello Tyler,
> I'm still interested in this issue. Do you have any update of the test?
> If
> you have any comments or questions, please feel free to let's know. We
> look
> forward to hearing from you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hello Tyler,
My Pleasure. :-)
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

IIS & Sql Server authentication

When would you use scenerio one vs. scenerio two?
Scenerio one - IIS (anonymous, or anonymous and
integrated)--OLE DB Provider for SQL (integrated
security)-->SQL (mixed mode or windows
authentication)
Scenerion two - IIS (basic or integrated)--OLE DB
Provider for SQL (integrated security)--> SQL (mixed
mode or windows authentication)
Our environment has IIS and SQL Server on the same machine.Hi Michelle,
Merry Christmas and thank you for using MSDN Newsgroup! It's my pleasure to
assist you with your issue.
You are choosing the authentication way for you application run on IIS and
SQL Server with OLE DB Provider, right? From my experience, to design a
system with high efficency and security will take many aspects for
consideration, including the application environment, security, connection,
performance requirment, etc. It is hard to say that one way is better than
another. It depends on specifics. So you can refer to the following
articles from Microsoft :
Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/ht
ml/SecNetch05.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/ht
ml/secnetlpMSDN.asp
Implementing a Secure Site with ASP
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsecure/ht
ml/msdn_implement.asp
Designing Efficient Applications for Microsoft SQL Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlsg/htm
l/msdn_designeff.asp
Accessing SQL Server from a Web Application
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/
vbconaccessingsqlserverfromwebapplication.asp
Configuring Security for Internet Information Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsentpro/ht
ml/veconConfiguringSecurityForInternetInformationServer.asp
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:
80/support/kb/articles/Q247/9/31.ASP&NoWebContent=1
Microsoft Internet Information Server Security Overview
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dniis/html/
iissecure.asp
Optimizing SQL Server and IIS Security and Connectivity
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9150
The following articles are from some other website. Microsoft does not
guarentee the correctness of it. You can still take them for reference:
Using Windows Security with IIS and SQL Server 2000
http://www.winnetmag.com/Articles/ArticleID/23035/pg/2/2.html
IIS User Authentication
http://www.adiscon.com/IIS/gen001.htm
Setting SQL Server 7.0 and IIS Security
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9002
Hope this would be helpful in solving your problem. If you still have
questions, please feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.