SQL Alert: situation

Showing posts with label situation. Show all posts

Tuesday, March 6, 2012

Access the filesystem with SQL server 2005

Hi,
We've got this strange situation. I want to perform a bulk insert in
SQLserver 2005 from a file on the file system. This works OK when I use a
SQLuser. When I do the same logged in with windows account (with
administrator privileges) I get an error. I thought that when I use a
windows account, this account is used to access the file system. When you
use a SQL account then the user of the sqlserver service is used to access
the filesystem. Am I wrong or how does this work ?
TIAYes, the windows account will need access to the file on the file system as
well as be a user with enough privledges on the database to perform the
insert. Are you using BCP or are you using SSIS to do the transfer?
--
Rob Walters
Program Manager - SQL Server
"Mark Brouwers" wrote:

> Hi,
> We've got this strange situation. I want to perform a bulk insert in
> SQLserver 2005 from a file on the file system. This works OK when I use a
> SQLuser. When I do the same logged in with windows account (with
> administrator privileges) I get an error. I thought that when I use a
> windows account, this account is used to access the file system. When you
> use a SQL account then the user of the sqlserver service is used to access
> the filesystem. Am I wrong or how does this work ?
> TIA
>
>|||Thanks for the reply,
I just use a query window within the management studio. I run the sql
statement as a SQLuser and it works. I change the connection properties to
my windows account (with administrator privileges) and is stops working.
Mark
"Rob Walters [MSFT]" <RobWaltersMSFT@.discussions.microsoft.com> wrote in
message news:4BBBCCE0-EFBD-4FD4-B046-B9C5371DA9E8@.microsoft.com...[vbcol=seagreen]
> Yes, the windows account will need access to the file on the file system
> as
> well as be a user with enough privledges on the database to perform the
> insert. Are you using BCP or are you using SSIS to do the transfer?
> --
> Rob Walters
> Program Manager - SQL Server
>
> "Mark Brouwers" wrote:
>

Saturday, February 25, 2012

Access rights to two mssql dbs via password protected role

I dont know how to arrange situation when application enduser needs to access data in two databases of mssql server concurently in those circumstances that access rights to the data should be restricted by password protected role (whose password is not known to the end user).

Detailed description of problem:

So far there was an application, that manipulated its data, saved in mssql server's database. End user authenticates to application by his (mssql server's) login name and password. The application authenticates the user by connecting to the database with the given name/password credentials, and then the application sets application role with hardcoded name/password. Thus application role sets the access rights for consequent end user's requests, delivered via application to the database server.

The goal is that end user cannot manipulate application database data when connects to the database by other means (e. g. via SQL server Manager), because he does not know the application role's password.

Now suppose that there are two applications (A1, A2), both using the same model for access restrictions. Each of them has its own database (A1DB, A2DB) and its own application role (A1R residing in A1DB, A2R residing in A2DB). End user (login) X can manipulate A1DB data when connects via A1, and A2DB data when connects via A2, and NO data when connects by other means.

Finally suppose that some subset of A2 data (let's say one table) is useful to see also via A1 application. There is no problem to add to A1DB view, that shows data from A2DB table together with A1DB tables. But when the user is connected via A1, he cannot see the data, because query on A1 view fails (user has not access rights on A2 data).

The access rights for A1 enduser cannot be set by no means i know because:

1) I cannot set the rights via public (guest) access because in that case they will be accessible to any users connected by any third party products, which is supposed to be security hole.

2) I cannot set the rights via dbuser or dbrole privileges, because they will not work when connected via A1 application (setting the app role suppresses the db privileges)

3) I cannot set the rights via application role because two application roles cannot be set concurrently.

4) I cannot abandon using application roles mechanism and use database roles mechanism, because db roles cannot be protected by independent password (not known to the enduser).

Please can anybody review my problem and either find the mistake in my approach, or propose other solution? So far I suppose the problem is my ignorance, because I am not great mssql expert.

I hope I can explain why approles will not work on your particular scenario. Approles are principals defined within a database, and they have absolutely no presence on the server or on other databases. Even if the 2 approles are named the same and have the same passwords, because they are defined in different databases they are not the same principals. The fact that after establishing an approle it is possible to go to a different DB as “guest” account (given that guest access is allowed) is a consequence of the previous SQL Server security model and that we have to maintain it for backwards compatibility reasons.

One potential solution for your problem would be to use the new impersonation mechanisms in SQL Server 2005. For example, you can create a module (i.e. a SP or a multistatement function) marked with EXECUTE AS (disable the login mapped to this user to prevent direct connections) with enough permission to allow that the impersonated context on A1DB can access the data on A2DB. Because you will be accessing cross-database data, you will need to either sign your module (recommended) or set the TRUSTWORTHY bit option ON on A1DB (source DB).

I would recommend the following references to understand this new impersonation model better:

· EXECUTE AS Clause http://msdn2.microsoft.com/en-us/library/ms188354.aspx

· Using EXECUTE AS in Modules http://msdn2.microsoft.com/en-us/library/ms178106.aspx

· Understanding Context Switching http://msdn2.microsoft.com/en-us/library/ms191296.aspx

· Understanding Execution Context http://msdn2.microsoft.com/en-us/library/ms187096.aspx

I also recommend reading Laurentiu’s blog as well as my own blog

· Laurentiu Cristofor’s blog http://blogs.msdn.com/lcris/

· Raul Garcia’s blog http://blogs.msdn.com/raulga/

I hope this information will be useful. Let us know if you have further questions.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to Mr. Garcia

Thank you very much for your answer. It seems you have solved my problem. In fact the focus of my problem was not in (not)understanding of role mechanism in mssql server. The problem was, that i was not able to convince my managers, that the concept of roles in mssql server does not allow simple setting of access rigthts to objects in two databases via password protected roles. They did not believed me, because were used to platforms, on which it is not problem at all. After reviewing your explanation it seems, that my managers will give me more time for solving our inter-application-communication problem via more complicated way - the new impersonating mechanism.