Connecting to SQL server?

Remember that IIS runs under a different account than you do when you debug.  Ensure that the account listed has permissions to access the database.  The easiest way to verify this is to open SQL Enterprise Manager, go to Security>Logins and add a new account or group to include the specified account, then in the Databases tab, check the database to give access to the account, and check the db_owner box in the bottom window.

Run again, and let me know the results.  This is almost always one of the first problems I run into with ASP.NET.  Normally, it is the ASPNET account, but I am assuming that the NETWORK SERVICE account is one either you or the server set up.

You can also test the database connectivity by using Query Analyzer to try and access the table.  If this works, then it might be your connection information, if you use the web.config file.

Keep me posted,

Bg

Is IIS and SQL on the same or different machines?? Also are you running in a Domain or Workgroup environment?

SQL server is running on the server. I am developing FROM a workstation onto the server. It is set up as the same domain as my workstation.

There is no such thing as NETWORK SERVICE account on my workstation or the SQL server login or the database users. But I have given access to guest account, my account, ASPNET account, domain computers and a bunch of others on the SQL database. No change.

Even if I set up a user on the SQL server to log in(not through windows integrated login) I still receive an error. Slightly different:

>Exception Details: System.Data.SqlClient.SqlException: >Login failed for user 'test'.

Test is the name of the account I set up in SQL server.
The thing I find strange is when I drop the tables onto the form (global.aspx) I can view the data in them(is this because I have access through windows integrated login on the SQL server?). But when I run it in a browser it will not let me do anything. What user to I have to give permission to? What if I wanted everyone on the internet to have access to this database-how would I do that. Thanks

SQL server is running on the server. I am developing FROM a workstation onto the server. It is set up as the same domain as my workstation.

There is no such thing as NETWORK SERVICE account on my workstation or the SQL server login or the database users. But I have given access to guest account, my account, ASPNET account, domain computers and a bunch of others on the SQL database. No change.

Even if I set up a user on the SQL server to log in(not through windows integrated login) I still receive an error. Slightly different:

>Exception Details: System.Data.SqlClient.SqlException: >Login failed for user 'test'.

Test is the name of the account I set up in SQL server.
The thing I find strange is when I drop the tables onto the form (global.aspx) I can view the data in them(is this because I have access through windows integrated login on the SQL server?). But when I run it in a browser it will not let me do anything. What user to I have to give permission to? What if I wanted everyone on the internet to have access to this database-how would I do that. Thanks

Your spot on about your windows integrated login stuff for why it works for you in design view.

What about IIS? Where is that located?

In some instances you need to allow the computers to be trusted for delegation, this is to ensure that the correct credentials are passed correctly between machines.

eg. client at workstation A, IIS on Server A, SQL on Server B.

client access hits the web page on Server A, server A authenticates the user and attempts to access the database on Server B.

What happens?? If impersonation is also in place Server A will appear as the authenticated user that is visiting the site. When server A attempts to access Server B this impersonation of the user is lost and suddenly the SQL server is attempted to be accesssed by an UNKNOWN ACCOUNT, and obviously hits an accessed denied.

If delegation is turned on, the impersonated credentials are passed through correctly to the SQL server and if that user has access to the server then all should be fine.

All that being said, I seem to recall that delegation only works if your IIS authentication is set to "Windows Authentication"

If you wanted to give everyone on the internet access to the database (ie no logon screen)

I would have the userID and password set in the connection string that you use to access the database. All visitors would use that connection string, using that user ID to connect to the database.

I've probably gone off track there, hope its helpful in someway. Time is of the essence, I'll post again if you need more.

Try switching the .Net server to IIS5 compatible mode. When .Net servers are run in IIS6 native mode most of the settings in config file get thrown out the window. IIS6 runs its own mode.
Give this is a shot.

Network Services account is intriduced in Windows .Net server. This accoutn is used for all the worker processes. ASP.Net runs as a worker process. This account has very minimal rights on the system.

I just confirmed this, and yes, there is a 'NT AUTHORITY\NETWORK SERVICES' account.  And yes, you cannot see it.

It is described here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dllproc/base/networkservice_account.asp

I suspect (I do not have .NET server yet), that if you add a database permission for Authenticated Users account (since the NetworkService account carries that SID) and enable the db_owner, you should then get connectivity.

I would recommend instead that you create a special account for this application's database connectivity only, and use Impersonation before your database calls using that account.  See WindowsIdentity.Impersonate in the .NET documentation for complete details on how to do this.

Hope this helps, but if you try this and it still doesn't work, please let me know.

Bg

1)How do you revert iis6 to iis5?

2)In order to allow an aspx program to run on the server I had to give access to "Authenticated Users" in the "C:\WINDOWS\Microsoft.NET\Framework\v1.0.3705\Temporary ASP.NET Files" but for the SQL server there is no "Authenticated Users"

3)Who is "sa"? In my web.config file I see: sqlConnectionString="data source=127.0.0.1;user id=sa;password=". "sa" is a user in the sql server but when I try to give him access it says: "Error 15405: Cannot use the reserved user or role name 'sa'"

4)Why don't I have access to the SQL database even when I run the program from the server Internet Explorer? Wouldn't the SQL database see that it is itself running it and allow access?

5)I have given access to the database for ASPNET. How do I give access to other worker processes?

6)My program runs upto the point where I try to access the database. Would it be possible to put some code in that will display what user the program is running under so I know who to give access to?

1)How do you revert iis6 to iis5?

2)In order to allow an aspx program to run on the server I had to give access to "Authenticated Users" in the "C:\WINDOWS\Microsoft.NET\Framework\v1.0.3705\Temporary ASP.NET Files" but for the SQL server there is no "Authenticated Users"

3)Who is "sa"? In my web.config file I see: sqlConnectionString="data source=127.0.0.1;user id=sa;password=". "sa" is a user in the sql server but when I try to give him access it says: "Error 15405: Cannot use the reserved user or role name 'sa'"

4)Why don't I have access to the SQL database even when I run the program from the server Internet Explorer? Wouldn't the SQL database see that it is itself running it and allow access?

5)I have given access to the database for ASPNET. How do I give access to other worker processes?

6)My program runs upto the point where I try to access the database. Would it be possible to put some code in that will display what user the program is running under so I know who to give access to?

3) sa is a System Admin account for SQL, kind of like the "Administrator" account for Windows. Generally, only the DBA has this account and gives each individual account the required database permissions (thus your login may grant you access to 1 BD, but not another).

4) Again, this depends on the authentication methods and login. If you are trying to use pass-through authentication (impersination enabled), the account you are logged into windows must have access to that DB. It sounds like you already have IIS and SQL on the same box, but remember that this must be true for identity impersonate to work using Integrated Windows Login. If they are on different machines, you will have to use Basic Authentication (clear-text).

6) Page.User.Identity.Name returns the userName that IIS sees. This will be Anonymous if you have anonymouse authentication on in IIS, your windows login if you have Integrated Windows Login enabled, or the account you used when the login box appears if you have Basic Authentication enabled.

3) sa is a System Admin account for SQL, kind of like the "Administrator" account for Windows. Generally, only the DBA has this account and gives each individual account the required database permissions (thus your login may grant you access to 1 BD, but not another).

4) Again, this depends on the authentication methods and login. If you are trying to use pass-through authentication (impersination enabled), the account you are logged into windows must have access to that DB. It sounds like you already have IIS and SQL on the same box, but remember that this must be true for identity impersonate to work using Integrated Windows Login. If they are on different machines, you will have to use Basic Authentication (clear-text).

6) Page.User.Identity.Name returns the userName that IIS sees. This will be Anonymous if you have anonymouse authentication on in IIS, your windows login if you have Integrated Windows Login enabled, or the account you used when the login box appears if you have Basic Authentication enabled.

With this set-up: VS.NET installed on workstation, SQL server and IIS setup of .Net server. To give myself access to the database and website(which accesses databases) how should I have the following set up for security?

1)VS.NET(on workstation)-integrated windows login or
                        -SQL server login

2)SQL server(on server)-integrated windows login or
           -integrated windows login and SQL server login

3)IIS webpage(on server)-anonymous login and/or
                        -integrated windows and/or
                   -digest auth. for windows domain and/or
                        -basic authentication or
                        -.Net passport

With the above authentication setup properly who do I give access to for the webpage(must be authenticated user-I think) and the SQL server for them to work together. Thanks

Forgot to add that for all the above axamples, you will want to set SQL to Windows Login.

<identity impersonate="true" /> - this did it. Thanks alot. I know this works but what is it doing exactly? Passing my username and password to the SQL server instead of using the worker process that is used for the aspnet program? Thanks

Pretty much.

This way you use the same account all the way through your site, from IIS to SQL and back. Makes it more secure and easier to track changes/error/events through your logs since you can look for the same account on all levels.