executing SQL from ASP.

Hi all,

I have been stuck on ths issue for over a week. Here goes I have created a website using ASP and SQLServer. Users are all able to execute STored procedures on the site but as soon as they try to execute SQL from a ASP page they get the following error:

HTTP 500.100 - Internal Server Error - ASP error

-----------------------------------------------

Microsoft OLE DB Provider for SQL Server (0x80040E09) SELECT permission denied on object 'tblPlatform', database 'Server_management', owner 'dbo'.

/serrver_management/searchKell.asp, line 571

I do not get this error, every other user does! ALthough I do not have a seperate SQLServer login I use the diomain LSL-UK\Domain Users.

I have a feeling it is something to do with IIS. Could it be because I am an IIS administrator and no one else is?

Any Help would be greatly appreciated.

Thanks

it is an sql server login issue. I'm not sure why you can access it when you run it because you shouldn't. Since you are running this from a web page, the user that is trying to access the database is ASPNET if you are using ASP.net and IUSR_<machine name> if you are running classic asp. You will need to specify the user and password in your connection string, or you can add the ASPNET/IUSR account to the database, or add the ASPNET/IUSR account to the Domain Users group.

What does your connectionString to the database look like?
If you are specifying a username in the connectionstring make sure that user has the correct permissions on the database.

>> I do not get this error, every other user does! ALthough
>> I do not have a seperate SQLServer login I use the
>> diomain LSL-UK\Domain Users.

You say everyone uses the same Domain User Name, but your using "LSL-UK\Domain Users" as the group for security?? I'm confused.. As a network guy, this is bad design.

I suggest if it's a public Web OR you have a lot of people using this website, then use a SQL Authentication. If it's internal and under 50 people, then use NT Security.

The ONLY reason this errors, is if the users you setup do not have rights to talk to that table. You need to go into Security | User, then make sure the users you using is setup to the right database. Then go under the database/Users and make sure you given these people rights to talk to the table. I'm guessing your not using Stored Procs, since your error in on the table and not on the proc. Also not so good.. Stored Procedures are always a better way to develop than inserting/updating/selecting direct from the ASP Page.

If your not using any NT Security, meaning, they are not prompted to put in a NETWORK username and password, and your not using SQL Authentication, meaning, your not using a username and password in the connection string, then YOU DO NOT WANT TO DO WHAT YOUR DOING! DO NOT setup Anonomous users to have rights to SQL unless you want a security hole as big as Texas on your hands.

Hi my connection string is:

strConnection = "Provider=SQLOLEDB;Integrated Security=SSPI;"
strConnection = strConnection & "Initial Catalog=Server_management;Data Source=cpmspodb5w;Connect Timeout=600"

Thing I don't understand is why I am the only person that can execute SQL from the ASP page.

Ps I am using classic ASP not ASP.NET....
Any ideas?

>>You say everyone uses the same Domain User Name, but your using "LSL-UK\Domain >>Users" as the group for security?? I'm confused.. As a network guy, this is bad design.

I meant we all use the same Domain as in "LSL-UK\SmithC asn LSL-UK\HatfieldM".

ALl the susers can excute the STored Procedures on my site but there is a complex search that i have created which I want to execute using SQL from the ASPO page, but I am the only person that seems to be able to execute this? and the only difference I can see between myself and anyone else is that I am an IIS addministrator?

Anybody please help......

remove integrated security from the connection string and replace it with a username and password and make sure that user is setup in sql server as a user for that database with the correct permissions.

Hi I was using the conn string below yesterday:

strConnection = "Provider=sqloledb;User ID=sa;Password=dbo;"
strConnection = strConnection & "Initial Catalog=Server_management;Data Source=cpmspodb5w;trusted_Connection=yes;"

But have changed it to: (cos the user group in SQL server is LSL-UK\Domain Users)

strConnection = "Provider=sqloledb;User ID=LSL-UK\Domain Users;Password=;"
strConnection = strConnection & "Initial Catalog=Server_management;Data Source=cpmspodb5w;trusted_Connection=yes;"

I dont know if this is imporatnt but when I take out trusted_Connection=yes i get the error Microsoft OLE DB Provider for SQL Server (0x80040E4D) Login failed for user 'LSL-UK\Domain Users'.

Since I have not set a password for LSL-UK\Domain Users is it Ok to leave it as Password=;

Users in LSL-UK\Domain Users can still execute all the stored procedures but cannnot perform a SELECT statement from within the ASP code???? and still get the errror
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E09)
SELECT permission denied on object 'tblPlatform', database 'Server_management', owner 'dbo'.

Although I have given the group every permission I see possible.

Can anyone please help?

Create a new sql server login.
Give it dbo permissions on the database and then use that username and password in your connectionstring.