Access protection on deployed SQL Server

How can we control the accesses to a SQL Server that has been deployed on customer site? Basically, we do not want user to log in the database server, even as sa, except through our application.

Any recommendation?

Thanks,

Xiaodoudou

Hi!

Actually you cannot protect the data files. So although you might restrict the user / administrators from logging on to the database you cannot prevent them deatching the database and attaching it to another instance, making it possible for them to read the content of the database. YOu would need additional file security and prevent the administrator and all other users from accessing the physical files.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

In your case, I assume you have no control over the customer site - if that's the case, then you cannot enforce access only through your application.

Thanks
Laurentiu

access problem

Hi,

The Report Server and the server hosting web application are differrent. I have an aspx page with the reportviewer control. If I try to open the page with the report control its giving me a message

privilages for the user 'domain/server$' are insufficient for performing this operation". where the 'server$' is the IIS server hosting webapplication.

Please help me in solving this

put Domain Administrator as a full user with all access permissions on the report server (Actually in the properties of the report)

Access Front End and SQL Server 2000 Record Level Access Control

Hello,

We are using Access ADP as a front end, SQL Server 2000 as a back end.
We have a customer contact database. We would like to limit certain
users to only receive certain records based on Windows NT group
membership.

For example, Eastern Sales Group can see clients located in their
region, but they cannot see clients located in the Northern Region.

Is there an elegant way to do this? Below a two solutions which have
been proposed, but none seem to fit. Access is required as a Front End
for its ease of use.

*********
** 1 **
*********
Add an additional attribute (bit mask value) to tblCustomers, and
query appropriately based on the user's group membership.

Problem:
Access allows users direct access to the underlying table.

*********
** 2 **
*********
Create a separe table for each group (effectively splitting
tblCustomers into smaller, separate tables based on group access).
Then, apply SQL Server security on the objects to enforce the business
rules.

Problem:
Does this break Normal Form? I've never seen a solution like this.

I've googled and found similar questions, but not a good solution.

Any suggestions would be appreciated.

Thanks,
Jeff
Jeffrey Walton
noloader.at.yahoo.com"Noloader" <noloader@.yahoo.com> wrote in message
news:6b543aa7.0404231447.777fe29c@.posting.google.c om...
> Hello,
> We are using Access ADP as a front end, SQL Server 2000 as a back end.
> We have a customer contact database. We would like to limit certain
> users to only receive certain records based on Windows NT group
> membership.
> For example, Eastern Sales Group can see clients located in their
> region, but they cannot see clients located in the Northern Region.
> Is there an elegant way to do this? Below a two solutions which have
> been proposed, but none seem to fit. Access is required as a Front End
> for its ease of use.
> *********
> ** 1 **
> *********
> Add an additional attribute (bit mask value) to tblCustomers, and
> query appropriately based on the user's group membership.
> Problem:
> Access allows users direct access to the underlying table.
> *********
> ** 2 **
> *********
> Create a separe table for each group (effectively splitting
> tblCustomers into smaller, separate tables based on group access).
> Then, apply SQL Server security on the objects to enforce the business
> rules.
> Problem:
> Does this break Normal Form? I've never seen a solution like this.
> I've googled and found similar questions, but not a good solution.
> Any suggestions would be appreciated.
> Thanks,
> Jeff
> Jeffrey Walton
> noloader.at.yahoo.com

In general, there is no reason why your users should need to access tables
directly. If you manage data access through stored procedures, then the
procedures can check role membership and only return or update those rows
which are permitted to the user. You could also use views, based on role
membership, or use application roles and handle everything in the client
application.

I would avoid solution 2, because it duplicates data - whatever information
you use to partition the data could also be put in a column. But if you have
a large amount of data, and if users only access their own data, then this
might also be an option.

Simon|||RE/
>We have a customer contact database. We would like to limit certain
>users to only receive certain records based on Windows NT group
>membership.

Based on what little experience I've had doing an Access front end against SQL
Server, I'd set SQL Server security so that nobody and nothing can get directly
to the tables in question and then develop stored procedures and/or views to
move the data back-and-forth between tables and app.
--
PeteCresswell

Access Denied when trying to execute a ssis task

Hi,

I sometimes come accross this error when I attempt to execute an isolated task in the control flow. What is funny is that I am still able to debug the package.

It eventually resolves after a while. What could it be?

Thanks

Philippe

TITLE: Microsoft Visual Studio

Access Denied. (Exception from HRESULT: 0x80030005(STG_E_ACCESSDENIED))

BUTTONS:

OK

Are you using source control?

You usually get this error if you execute something that isn't checked out.

-Jamie

|||

Yep, I do use VSS 6

I have a hard time understanding a few things with VSS, i.e. I was running a package fine in BIDS but it failed in SSMS, The package seemed checked-in but the solution was checked out in another location under the same userid.

I did force the undo check out and it eventually went fine. Now, SSMS is running the version I want.

I yet have to understand exactly how VSS Works. Right now, I am confused.

Philippe

|||The point is that you shouldn't have to check an SSIS package out of source control (we use TFS here) in order to execute it. This mis-feature is still present 15 months after Philippe's message, and still very frustrating!|||

I agree.

Have you reported this at Connect?

-Jamie

|||

Hi,

I have dropped VSS. I now use my company "official" source control, CVS, along with a couple third party tools like Tortoise, Smart CVS and CVSSCC which gives me some integration right from dev tools.

All that source control stuff is still really not user friendly, because of that I keep multiple backups of my stuff.

This has saved my life a couple times.

I thought the point of using any source control was to free the developer of any concern about these things and let him focus on development. I was wrong.

My IT chose CVS only because it is free, not because it is good, on the top of that they run an outdated unix version on a very old box.

I have a hard time with this while it is supposed to be "safer".

I need to be really carefull with this, not feeling safe anyhow.

Does any one has a success story to share as far as source control of any kind is concerned?

- Multiple developers on the same project

- Deployment of specifc versions by a dba team

- roll back to previous versions

- branches management

- never loosing any file/version on your working folder

- no fuss with roots/modules

- no issues with caps/no caps version fo the same module

- always get the project to the right root/module

- able to easilly do spring cleaning of the repository

Thanks,

Philippe

Access Denied when trying to execute a ssis task

Hi,

I sometimes come accross this error when I attempt to execute an isolated task in the control flow. What is funny is that I am still able to debug the package.

It eventually resolves after a while. What could it be?

Thanks

Philippe

TITLE: Microsoft Visual Studio

Access Denied. (Exception from HRESULT: 0x80030005(STG_E_ACCESSDENIED))

BUTTONS:

OK

Are you using source control?

You usually get this error if you execute something that isn't checked out.

-Jamie

|||

Yep, I do use VSS 6

I have a hard time understanding a few things with VSS, i.e. I was running a package fine in BIDS but it failed in SSMS, The package seemed checked-in but the solution was checked out in another location under the same userid.

I did force the undo check out and it eventually went fine. Now, SSMS is running the version I want.

I yet have to understand exactly how VSS Works. Right now, I am confused.

Philippe

|||The point is that you shouldn't have to check an SSIS package out of source control (we use TFS here) in order to execute it. This mis-feature is still present 15 months after Philippe's message, and still very frustrating!|||

I agree.

Have you reported this at Connect?

-Jamie

|||

Hi,

I have dropped VSS. I now use my company "official" source control, CVS, along with a couple third party tools like Tortoise, Smart CVS and CVSSCC which gives me some integration right from dev tools.

All that source control stuff is still really not user friendly, because of that I keep multiple backups of my stuff.

This has saved my life a couple times.

I thought the point of using any source control was to free the developer of any concern about these things and let him focus on development. I was wrong.

My IT chose CVS only because it is free, not because it is good, on the top of that they run an outdated unix version on a very old box.

I have a hard time with this while it is supposed to be "safer".

I need to be really carefull with this, not feeling safe anyhow.

Does any one has a success story to share as far as source control of any kind is concerned?

- Multiple developers on the same project

- Deployment of specifc versions by a dba team

- roll back to previous versions

- branches management

- never loosing any file/version on your working folder

- no fuss with roots/modules

- no issues with caps/no caps version fo the same module

- always get the project to the right root/module

- able to easilly do spring cleaning of the repository

Thanks,

Philippe

Access denied error with reporting service (ReportViewer)

I have a web page that has ReportViewer control in it. I load a report to it and it comes there nice and everything is ok. In report I have a link that opens another web page from the same web site where the original page is located (lets say that the original is in http//MySite and the page opened from the link is in MySite/Dialogs/MyDialog.aspx). The new page is opened with javascript function window.open(...) This new page contains reportviewer control, too and it's populated by the parameters given in the link url.

When I open the dialog page in the computer that has the web page and the report server on the same computer everything runs smoothly. But when I try to use the application from another computer it doesn't work right. The main page and the report opens to the browser in a proper way but when I click to the link that should open the dialog new browser window is opened but the reportviewer gives http 401 access denied error with ServerReport.SetProperties method.

I have set impersonate=true to the web config and I'm administrator in both computers that are used. What's the problem here? In another evironment it works nicely, but when the web site is used from another computer it gets broken...

Does ur report server have any credentials that u need to pass?

|||

What credentials should you have? I'm using windows authentication in my web page (set in web.config). Does the report server has a user account that should be set to the control?

|||

Suppose if i am trying to access my sql server data base from a different computer, windows authentication wont let me get into it... u will need a Username and password.. like sa or something like...

So i think u would need the same thing for report server... if i specify local host and the reports are hosted on ur computer i dont need any username or password to get in to the report server .. Suppose if u want to access it from a different computer and then give local host ... its gonna look in the local server and naturally not let u in to the report server...

regards,

Karen

|||

The link url doesn't point to localhost. It has the name of the computer where the report server is running (like http//MyAnotherComputer/ReportPage/ReportDialog.aspx). So I'm not pointing to localserver at any time. Even in the environment where the the application is working I use the name of the computer in the link url.

|||

then instead of the machine name ... try giving the ipaddress of that machine and one more when u try to access... http:/ServerName/Reports... does it prompt you for a username and password to get into it and view the reports?

|||Why would it make any difference to set ip address instead of computer name? The computers are both in the sam domain so there shouldn't be any problem with that. I gotta try the second test first time when I get back to my development computer. I'll let you know the result then.|||Why would it make any difference to set ip address instead of computer name? The computers are both in the same domain so there shouldn't be any problem with that. I gotta try the second test (accessing reports from another computer) first time when I get back to my development computer. I'll let you know the result then.|||Ok, I tried to access the report server with a browser on another computer (the url used washttp://computer_name/Reports) and worked just fine. Could it be possible that when I open another window from a report using a link, that the gredentials don't follow to the second window? If this is a case, what user account information should be forwarded to the dialog window? Should I use Web Service identity that is set for the Report Server? If I have set impersonate true in web.config, how does it affect to the behaviour of the control?|||I got this fixed! It seems that the application made a double hop. I made a mistake and called a report server on another computer instead of the one that was running on the web server.