How can IIS access the database without having ASPNET account in the database

[Tony Johansson]

Hello!

I have a CustomerData.aspx with two controls one SqlDataSource and one
DataGrid that is listing every Customer in the Northwind traders database. I
run the CustomerData from visual studio 2005 using the build in Development
Server so I use the File system. This works perfect

To make it work from IIS I created a virtual directory that points the the
physical path where my web site is located.
Now to the strange thing I just tried to run this page
http://localhost/Northwind/customerdata.aspx from the brower and
it worked without having added any account for ASPNET in Sql server.
As we all know it's acoount ASPNET that IIS is using when running aspx page
So I just entered this url http://localhost/Northwind/customerdata.aspx
into the browser and it works perfect.

It's correct that it worked when I run it from within visual studio 2005
because it was run under the account that I have used when logging into the
computer but as far as I can understand it should not work when running
under IIS because IIS is using ASPNET and there is no such account in Sql
server.

//Tony

 

[Gregory A. Beamer]

To make it work from IIS I created a virtual directory that points the
the physical path where my web site is located.
Now to the strange thing I just tried to run this page
http://localhost/Northwind/customerdata.aspx from the brower and
it worked without having added any account for ASPNET in Sql server.
As we all know it's acoount ASPNET that IIS is using when running aspx
page So I just entered this url
http://localhost/Northwind/customerdata.aspx into the browser and it
works perfect.

There are quite a few reasons this might be working.

1. You did the drag and drop routine and left the default of integrated
security on. This means it is checking if you have rights to SQL Server,
which you do, since you installed it.

2. You altered the connection string to have a user name and password.

Neither of these options are magic, except perhaps that you did not
reallize that allowing the drag and drop to work meant it was trying to
see if YOU had access. it has nothing to do with ASPNET user while it is
on your dev box.

Open the web.config and look at the connection strings section. That is
where 100% of the connection "magic" happens. If it is an Internet app,
you will most likely want a user name/password in the connection string
(almost always for ISP sites). Or you can set up impersonation and
impersonate an account with access.

Peace and Grace,

--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Tony Johansson]

I looked in the web.config and here is the connectionStrings element
<connectionStrings>
<add name="NorthwindConnectionString" connectionString="Data
Source=HEMPC\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>

As you can see I use the Integrated Security.
I don't understand I installed Sql server under my account which is Tony and
not ASPNET for that reason
I must ask you again what do you mean with this. You did the drag and drop
routine and left the default of integrated
security on. This means it is checking if you have rights to SQL Server,
which you do, since you installed it.

So what is the difference from Sql Server point of view when I use my
account Tony when accessing the database from Visual Studio and when IIS is
using ASPNET to access the sql Server.

Can you specify which account in the Sql Server that make this possible to
use Integrated Security for account Tony and account ASPNET ?

As you can see from web.config I use Sql Server express 2005


//Tony

 

[Gregory A. Beamer]

I looked in the web.config and here is the connectionStrings element
<connectionStrings>
<add name="NorthwindConnectionString" connectionString="Data
Source=HEMPC\SQLEXPRESS;Initial Catalog=Northwind;Integrated
Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

As you can see I use the Integrated Security.
I don't understand I installed Sql server under my account which is
Tony and not ASPNET for that reason
I must ask you again what do you mean with this. You did the drag and
drop routine and left the default of integrated
security on. This means it is checking if you have rights to SQL
Server, which you do, since you installed it.

When you use integrated security, the app essentially logs you in. If
you want to test this, make another account (after you have the website
in IIS) and make sure that account does not have access to SQL Server.
You will see that it fails. Most likely the browser will pop up so you
can "log in", but I am not sure of that (have to think about it). If it
does, you can log in as Tony (you state this is your account) and gain
access.

The implications here is everyone needs a Windows account to use the
application.

If you changed to this:

<connectionStrings>
<add name="NorthwindConnectionString" connectionString="Data
Source=HEMPC\SQLEXPRESS;Initial Catalog=Northwind;UID=
{sqlAccountsWithRights};PWD={AccountPassword}"
providerName="System.Data.SqlClient"/>
</connectionStrings>

It would work for everyone. Note that the {} mean you have to change the
item to an actual account and password and not type this verbatim. This
mens you are now using a SQL account rather than INTEGRATED Windows
acccount (meaning it is INTEGRATED into the app by a Windows Login,
either implied (as it is in your case) or explicit (as in the log in
when you change to another account that does not have access)).

So what is the difference from Sql Server point of view when I use my
account Tony when accessing the database from Visual Studio and when
IIS is using ASPNET to access the sql Server.

SQL Server knows two types of accounts:

1. Windows
2. SQL

That is it. When you use Integrated in a connection string, you are
stating "the windows user must have access". Whether you are using
Visual Studio or IIS is inconsequential; you are still using a windows
account.

Now, if you asked "why can I get to the database from Visual Studio and
not when I start the web app", the reason would be improper set up of
the browser or IIS. In IIS, it would likely be the lack of integrated
security being checked. In the browser, it would be a lot of hacking at
the defaults (at least in newer versions of IE) to not allow pass
through of accounts (not even sure it can be done any more, but assume
it can). If you hit with a really old version of IE, the default would
force a login popup when you hit something you don't have access to.

Can you specify which account in the Sql Server that make this
possible to use Integrated Security for account Tony and account
ASPNET ?

ASPNET has NOTHING to do with this right now. Your browser knows Tony is
the account being run and passes that account information to the app.
The app then uses that for SQL Server. This is not completely true, as
the web app tries ASPNET first (anonymous) and then reverts to your user
information, so it is a bit more complex.

Right now, ASPNET has no rights to SQL, so a bit of "magic" happens and
you are accessing as Tony. If you had full SQL Server, you could use
profiler and see the account logon.

if you throw this out on an ISP, you will have a huge fail, unless you
have an actual windows account on their server. And if you do have an
account, you will have to login, unless that account almost perfectly
matches your local account. As this is unusual, you will get a login and
login with your ISP account and get in. All of your customers will not
have this information and fail.

Hopefully this makes things clearer.

Peace and Grace,

--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Tony Johansson]

Sorry to bother you again but as you say when I use integrated security all
accounts that is defined in windows will automatically have access to Sql
server.
I ask you is this correct ?
I say yes !

If I click on My computer and then select manage and then expand item
"select local user and groups". Now I click on users and all defined users
are listed. Here I can see user ASPNET and user Tony and some other.

In your mail you write that

I don't understand what you mean here ?
When you use integrated security, the app essentially logs you in. If
you want to test this, make another account (after you have the website
in IIS) and make sure that account does not have access to SQL Server.
You will see that it fails. Most likely the browser will pop up so you
can "log in", but I am not sure of that (have to think about it). If it
does, you can log in as Tony (you state this is your account) and gain
access.

IIS always use account ASPNET no matter what account I have logged in as.
I ask you is this correct ?
I say yes !

Even if I want to test this as you mentioned I can create a new account for
example TonyTest but that will not help anything
because ASPNET will still be used by IIS. So what do you mean by writing I
can test this by making another account and making sure that the account
doesn't have access to Sql server. I haven't the slighest idea what you mean
here ? Can you guide me in the right direction ?
As we all know all window account have always automatically access to Sql
server when using integrated security.

Here you write in your previous mail.
Right now, ASPNET has no rights to SQL, so a bit of "magic" happens and
you are accessing as Tony. If you had full SQL Server, you could use
profiler and see the account logon.
But when I wrote something in the browser using IIS which is using ASPNET
I didn't receive any kind of access problem.So what do you mean saying
ASPNET has no right to SQL.
But when you use Integrated Security with a windows account that account has
full access to sql server

//Tony

 

[Gregory A. Beamer]

Sorry to bother you again but as you say when I use integrated
security all accounts that is defined in windows will automatically
have access to Sql server.
I ask you is this correct ?
I say yes !

No. The following will have access:

1. Any user explicitly given access to SQL Server
2. Any user in a group explicitly given access to SQL Server

Chances are, since you probably used defaults, your account is an
administrator. The following normally have access to SQL Server:

1. Administrators on the box (unless explicitly removed)
2. The user that installed SQL Server (unless removed during install or
afterward)
3. Built in SQL Accounts (which have a random password unless you set
one up for them)
4. SQL accounts

By default, there is a super account called sa. If you set an sa
password, you can use that account.

NOTE: By default, Express installs in Windows only mode. You can set it
up with mixed authentication and then set up SQL accounts.

If I click on My computer and then select manage and then expand item
"select local user and groups". Now I click on users and all defined
users are listed. Here I can see user ASPNET and user Tony and some
other.

If you want to give ASPNET full rights to SQL Server, you can add it to
the admin group, or an explicit SQL group. Or you can go into SQL Server
Manager and give it rights. I do not suggest doing this.

The better option is to switch to mixed mode:
http://www.hosting.com/support/vps/windows/mixedmode/

And then create an account that you can duplicate on another SQL Server.
If you are going to an ISP, then use the same name and password as the
account you have on the ISP and name the database the same name. Then
the only change in the connection string when you push the site is to
change the server name.

In summary

1. Unless you have given ASPNET rights, or you have put ASPNET in a
group that has rights, it has NO rights to SQL Server.

TEST condition
Create new non-admin account (user only account) on your box and log in
and try to use the application

2. You are getting to the server, as it recognizes your account as an
account with access.
a) This is most likely due to you being an admin on the box and the
admin group in the SQL Server admins role (default condition)
b) This is accomplished by the browser handing your credentials when the
app asks who you are

I know it is hard when you are first starting out with this. The best
thing to learn is how security happens on windows and then how SQL is
wired into that security. You then go to how IIS maps security with
different authentication modes and it makes sense. I guess I will have
to blog about this.

Peace and Grace,

--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Tony Johansson]

I use windows XP with Sql server express 2005 on the same machine.

What you say doesn't match the result that I get when testing.
I did the following.
1. I created a user with name Test and put this Test in Group guest.
2. I logged in to the machine with user Test
3. I just tested if I was allowed to start Sql Server Management Studio
Express and I was allowed.
4. Now I created a web site with the following.
Two controls one SqlDataSource and one DataGrid on the same aspx page. I
used window authentisering.
I configure SqlDataSource with select * from Customer and used the
Norhwind database.
5. I run the built in visual studio development server so the File System
was used.
6.I did nothing in the sql server
7.I just run the aspx page from within visual studio and it works perfect
with this test account
that is a member of Guest.

I think you might be wrong because it seems to me that all accounts that I
use with Windows authentisering
has full access to sql server.Even this Test account that ONLY was a member
in guest group.


//Tony

 

[Gregory A. Beamer]

I think you might be wrong because it seems to me that all accounts
that I use with Windows authentisering
has full access to sql server.Even this Test account that ONLY was a
member in guest group.

Let's summarize up front:

1. Only two ways to get to SQL Server (for this conversation, as app
roles do not count and only confuse the matter)
a) Windows account with SQL rights
b) SQL Server account
2. Various settings determine which account is used to connect with
integrated security
a) IIS settings (security primarily)
b) Internet Explorer settings
c) Web config settings
3. SQL Server will check that account for permissions. If yes, then you
see the data.

There is something on your machine that is mapping you to an account
that has permissions. i am not sure what this is, as I don't have all of
the variables. This means I can only talk in generalities.

string user = User.Identity.Name;

Try this in your code. If you are using DataSource, you might not see
this, so you can do something like this:

protected void Page_Load(object sender, EventArgs e)
{
string user = User.Identity.Name;
DataSet ds = GetData();

GridView1.DataSource = ds;

}

private DataSet GetData()
{
string connectionString = ConfigurationManager.ConnectionStrings
[{MyConnectionString}].ConnectionString;
string sql = "{sample SQL}";

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sql, connection);

SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();

string y = User.Identity.Name.ToString();

try
{
connection.Open();
adapter.Fill(ds);
}
finally
{
connection.Dispose();
}

string user = User.Identity.Name.ToString();

return ds;
}

Alter to your connection string names etc. Change everything in {}. Add
breakpoints and watch the user account. That is whom you are logging in
as. If the account is Test, it has permissions to SQL Server somewhere.

What I can tell you is how SQL Server works.

A. There are only two ways to log into SQL Server (at least from the
standpoint of this conversation)

1. A SQL Account
2. A Windows login/group account with SQL permissions

B. There are only two ways to set up SQL Server for accounts:

1. Windows only mode - only accepts windows logins
2. Mixed mode - both Windows and SQL accounts

When you create a new account on windows, if it belongs in a group with
SQL permissions, it has permissions to SQL Server. If not, you must
either explicitly give it permissions or have it in a group that has
permissions.

At this point, I don't know what groups you have mapped to what in SQL
Server. I also don't have a default install of Express, so I can't test
what the default does.

If I create a new login in SQL Server, in mixed mode, I can either map a
local user or create a SQL account. The GUi allows this, or I can use
CREATE LOGIN:

USE [master]
GO

--WINDOWS
CREATE LOGIN [{DOMAIN\MACHINE}\{UserName}] FROM WINDOWS WITH
DEFAULT_DATABASE=[{WhateverDbIsDefault}]
GO

--SQL
CREATE LOGIN [{UserName}] WITH PASSWORD=N'{Password}', DEFAULT_DATABASE=
[{WhateverDbIsDefault}], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

If I am logged in with the first account, the following connection
string works:

server=(local);database=blah;Integrated Security=true;

But, if I log in with a user without rights to SQL Server, it fails.
This happens 100% of the time. If I can still get in, something is
mapping me to a SQL account. There is no way around this.

Why no way? SQL essentially borrows from the Windows security system
(reason for Windows auth only as default). The SQL authentication is
added on top to get around having to create machine/domain accounts for
all users. It is, underneath the hood, mapping to a windows account, at
least in the newer versions (which allow expiration of accounts). that
is probably deeper than we need to go.

I can choose to not expire the account (as in the second CREATE LOGIN
call) and not have the account use windows account policy (also in
string) and it becomes a permanent account that I can use for my web
apps.



--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************