Hi all!
I have msde2000 on one machine. want to connect to it from other machine. both machines are connected via a switch with integrated firewall.
Do I need to set up this firewall to enable connections? Like opening ports for msde in the virtual servers settings of the firewall?
If I do this, will that make my sql-server visible and accessible to the internet?
Or should I be able to find my sql server without such steps. And if so how?
networking is up and i can access folders of both machines via the intranet. Is that enough or do i need more settings here?
Let's say the machine and thus the server is called Alpha. What goes into my connectionstring from the other machine?
this wont be enough, right?
strconn = "Provider=SQLOLEDB;" & _
"Data Source="Alpha";"
Please shed some light on these questions.
tia
KumaFirst you will have to configure the ODBC settings in your machine. Make sure the datasource name is "alpha" , the same as the server name. You can then use "alpha" in the connection string. The code that is required is
'VB Code starts here
Set db = New ADODB.Connection
db.Properties("Data Source").Value = "alpha"
db.Properties("Initial Catalog").Value = "Northwind"
db.Properties("User ID").Value ="XXX"
db.Properties("Password").Value = "YYYYY"
db.Open
'ASP Code starts here
Set db = Server.CreateObject("ADODB.Connection")
db.Open "Provider=SQLOLEDB;Data Source=alpha;Initial Catalog=Northwind;User Id=XXX;Password=YYY"
'CODE ENDS HERE
Roshmi Choudhury
Showing posts with label integrated. Show all posts
Showing posts with label integrated. Show all posts
Sunday, 25 March 2012
Wednesday, 7 March 2012
Confused with security
I thought I understood what was happening - obviously not.
Here is my problem:
Using windows integrated security.
No users should have access to the table directly.
I have a UDF that I have granted select, insert, update, and delete on to a
created roll that has windows users added to it.
The user can select through the function ok. but when they try to insert,
they get an error complaining of lack of rights to insert.
I had to add insert rights to the table for the user to insert through the
function.
Is it becuse the function is a select statement, and it dynamically creates
the update, insert methods directly against the table and by-passes the
function and that is my problem?
Thanks for any clarification.
On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:
>I thought I understood what was happening - obviously not.
>Here is my problem:
>Using windows integrated security.
>No users should have access to the table directly.
>I have a UDF that I have granted select, insert, update, and delete on to a
>created roll that has windows users added to it.
>The user can select through the function ok. but when they try to insert,
>they get an error complaining of lack of rights to insert.
>I had to add insert rights to the table for the user to insert through the
>function.
>Is it becuse the function is a select statement, and it dynamically creates
>the update, insert methods directly against the table and by-passes the
>function and that is my problem?
>Thanks for any clarification.
>
Hi Howard,
Your guess is correct - welcome to the pitfalls of dynamic SQL.
If a stored procedure references a table owned by the same userid that
owns the stored procedure, no additional check for access right to that
table is made. Each user that has rights to execute the procedure can
access that table through that procedure. Other means of access to the
table still need explicit permissions for that user on the table!
Dynamic SQL is executed in a seperate environment, just as if the user
exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
that this is started from a stored procedure. Therefor, the ownership
chain from procedure to table breaks when dynamic SQL is executed.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks so much Hugo, I thought I was really lost!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:teo9j0l1fnknj0frfq3l0u6hgs2s5oh9bm@.4ax.com... [vbcol=seagreen]
> On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:
a[vbcol=seagreen]
the[vbcol=seagreen]
creates
> Hi Howard,
> Your guess is correct - welcome to the pitfalls of dynamic SQL.
> If a stored procedure references a table owned by the same userid that
> owns the stored procedure, no additional check for access right to that
> table is made. Each user that has rights to execute the procedure can
> access that table through that procedure. Other means of access to the
> table still need explicit permissions for that user on the table!
> Dynamic SQL is executed in a seperate environment, just as if the user
> exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
> that this is started from a stored procedure. Therefor, the ownership
> chain from procedure to table breaks when dynamic SQL is executed.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Here is my problem:
Using windows integrated security.
No users should have access to the table directly.
I have a UDF that I have granted select, insert, update, and delete on to a
created roll that has windows users added to it.
The user can select through the function ok. but when they try to insert,
they get an error complaining of lack of rights to insert.
I had to add insert rights to the table for the user to insert through the
function.
Is it becuse the function is a select statement, and it dynamically creates
the update, insert methods directly against the table and by-passes the
function and that is my problem?
Thanks for any clarification.
On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:
>I thought I understood what was happening - obviously not.
>Here is my problem:
>Using windows integrated security.
>No users should have access to the table directly.
>I have a UDF that I have granted select, insert, update, and delete on to a
>created roll that has windows users added to it.
>The user can select through the function ok. but when they try to insert,
>they get an error complaining of lack of rights to insert.
>I had to add insert rights to the table for the user to insert through the
>function.
>Is it becuse the function is a select statement, and it dynamically creates
>the update, insert methods directly against the table and by-passes the
>function and that is my problem?
>Thanks for any clarification.
>
Hi Howard,
Your guess is correct - welcome to the pitfalls of dynamic SQL.
If a stored procedure references a table owned by the same userid that
owns the stored procedure, no additional check for access right to that
table is made. Each user that has rights to execute the procedure can
access that table through that procedure. Other means of access to the
table still need explicit permissions for that user on the table!
Dynamic SQL is executed in a seperate environment, just as if the user
exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
that this is started from a stored procedure. Therefor, the ownership
chain from procedure to table breaks when dynamic SQL is executed.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks so much Hugo, I thought I was really lost!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:teo9j0l1fnknj0frfq3l0u6hgs2s5oh9bm@.4ax.com... [vbcol=seagreen]
> On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:
a[vbcol=seagreen]
the[vbcol=seagreen]
creates
> Hi Howard,
> Your guess is correct - welcome to the pitfalls of dynamic SQL.
> If a stored procedure references a table owned by the same userid that
> owns the stored procedure, no additional check for access right to that
> table is made. Each user that has rights to execute the procedure can
> access that table through that procedure. Other means of access to the
> table still need explicit permissions for that user on the table!
> Dynamic SQL is executed in a seperate environment, just as if the user
> exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
> that this is started from a stored procedure. Therefor, the ownership
> chain from procedure to table breaks when dynamic SQL is executed.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Confused with security
I thought I understood what was happening - obviously not.
Here is my problem:
Using windows integrated security.
No users should have access to the table directly.
I have a UDF that I have granted select, insert, update, and delete on to a
created roll that has windows users added to it.
The user can select through the function ok. but when they try to insert,
they get an error complaining of lack of rights to insert.
I had to add insert rights to the table for the user to insert through the
function.
Is it becuse the function is a select statement, and it dynamically creates
the update, insert methods directly against the table and by-passes the
function and that is my problem?
Thanks for any clarification.Check who owns the objects. Ownership chain may be broken
(see BOL). In short a person who does not have ownership
of the underlying object cannot get rights through a
view/function etc that they own.
>--Original Message--
>I thought I understood what was happening - obviously not.
>Here is my problem:
>Using windows integrated security.
>No users should have access to the table directly.
>I have a UDF that I have granted select, insert, update,
and delete on to a
>created roll that has windows users added to it.
>The user can select through the function ok. but when
they try to insert,
>they get an error complaining of lack of rights to insert.
>I had to add insert rights to the table for the user to
insert through the
>function.
>Is it becuse the function is a select statement, and it
dynamically creates
>the update, insert methods directly against the table and
by-passes the
>function and that is my problem?
>Thanks for any clarification.
>
>.
>|||On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:
>I thought I understood what was happening - obviously not.
>Here is my problem:
>Using windows integrated security.
>No users should have access to the table directly.
>I have a UDF that I have granted select, insert, update, and delete on to a
>created roll that has windows users added to it.
>The user can select through the function ok. but when they try to insert,
>they get an error complaining of lack of rights to insert.
>I had to add insert rights to the table for the user to insert through the
>function.
>Is it becuse the function is a select statement, and it dynamically creates
>the update, insert methods directly against the table and by-passes the
>function and that is my problem?
>Thanks for any clarification.
>
Hi Howard,
Your guess is correct - welcome to the pitfalls of dynamic SQL.
If a stored procedure references a table owned by the same userid that
owns the stored procedure, no additional check for access right to that
table is made. Each user that has rights to execute the procedure can
access that table through that procedure. Other means of access to the
table still need explicit permissions for that user on the table!
Dynamic SQL is executed in a seperate environment, just as if the user
exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
that this is started from a stored procedure. Therefor, the ownership
chain from procedure to table breaks when dynamic SQL is executed.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks so much Hugo, I thought I was really lost!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:teo9j0l1fnknj0frfq3l0u6hgs2s5oh9bm@.4ax.com...
> On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:
> >I thought I understood what was happening - obviously not.
> >
> >Here is my problem:
> >
> >Using windows integrated security.
> >No users should have access to the table directly.
> >
> >I have a UDF that I have granted select, insert, update, and delete on to
a
> >created roll that has windows users added to it.
> >The user can select through the function ok. but when they try to insert,
> >they get an error complaining of lack of rights to insert.
> >I had to add insert rights to the table for the user to insert through
the
> >function.
> >Is it becuse the function is a select statement, and it dynamically
creates
> >the update, insert methods directly against the table and by-passes the
> >function and that is my problem?
> >
> >Thanks for any clarification.
> >
> Hi Howard,
> Your guess is correct - welcome to the pitfalls of dynamic SQL.
> If a stored procedure references a table owned by the same userid that
> owns the stored procedure, no additional check for access right to that
> table is made. Each user that has rights to execute the procedure can
> access that table through that procedure. Other means of access to the
> table still need explicit permissions for that user on the table!
> Dynamic SQL is executed in a seperate environment, just as if the user
> exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
> that this is started from a stored procedure. Therefor, the ownership
> chain from procedure to table breaks when dynamic SQL is executed.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Here is my problem:
Using windows integrated security.
No users should have access to the table directly.
I have a UDF that I have granted select, insert, update, and delete on to a
created roll that has windows users added to it.
The user can select through the function ok. but when they try to insert,
they get an error complaining of lack of rights to insert.
I had to add insert rights to the table for the user to insert through the
function.
Is it becuse the function is a select statement, and it dynamically creates
the update, insert methods directly against the table and by-passes the
function and that is my problem?
Thanks for any clarification.Check who owns the objects. Ownership chain may be broken
(see BOL). In short a person who does not have ownership
of the underlying object cannot get rights through a
view/function etc that they own.
>--Original Message--
>I thought I understood what was happening - obviously not.
>Here is my problem:
>Using windows integrated security.
>No users should have access to the table directly.
>I have a UDF that I have granted select, insert, update,
and delete on to a
>created roll that has windows users added to it.
>The user can select through the function ok. but when
they try to insert,
>they get an error complaining of lack of rights to insert.
>I had to add insert rights to the table for the user to
insert through the
>function.
>Is it becuse the function is a select statement, and it
dynamically creates
>the update, insert methods directly against the table and
by-passes the
>function and that is my problem?
>Thanks for any clarification.
>
>.
>|||On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:
>I thought I understood what was happening - obviously not.
>Here is my problem:
>Using windows integrated security.
>No users should have access to the table directly.
>I have a UDF that I have granted select, insert, update, and delete on to a
>created roll that has windows users added to it.
>The user can select through the function ok. but when they try to insert,
>they get an error complaining of lack of rights to insert.
>I had to add insert rights to the table for the user to insert through the
>function.
>Is it becuse the function is a select statement, and it dynamically creates
>the update, insert methods directly against the table and by-passes the
>function and that is my problem?
>Thanks for any clarification.
>
Hi Howard,
Your guess is correct - welcome to the pitfalls of dynamic SQL.
If a stored procedure references a table owned by the same userid that
owns the stored procedure, no additional check for access right to that
table is made. Each user that has rights to execute the procedure can
access that table through that procedure. Other means of access to the
table still need explicit permissions for that user on the table!
Dynamic SQL is executed in a seperate environment, just as if the user
exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
that this is started from a stored procedure. Therefor, the ownership
chain from procedure to table breaks when dynamic SQL is executed.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks so much Hugo, I thought I was really lost!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:teo9j0l1fnknj0frfq3l0u6hgs2s5oh9bm@.4ax.com...
> On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:
> >I thought I understood what was happening - obviously not.
> >
> >Here is my problem:
> >
> >Using windows integrated security.
> >No users should have access to the table directly.
> >
> >I have a UDF that I have granted select, insert, update, and delete on to
a
> >created roll that has windows users added to it.
> >The user can select through the function ok. but when they try to insert,
> >they get an error complaining of lack of rights to insert.
> >I had to add insert rights to the table for the user to insert through
the
> >function.
> >Is it becuse the function is a select statement, and it dynamically
creates
> >the update, insert methods directly against the table and by-passes the
> >function and that is my problem?
> >
> >Thanks for any clarification.
> >
> Hi Howard,
> Your guess is correct - welcome to the pitfalls of dynamic SQL.
> If a stored procedure references a table owned by the same userid that
> owns the stored procedure, no additional check for access right to that
> table is made. Each user that has rights to execute the procedure can
> access that table through that procedure. Other means of access to the
> table still need explicit permissions for that user on the table!
> Dynamic SQL is executed in a seperate environment, just as if the user
> exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
> that this is started from a stored procedure. Therefor, the ownership
> chain from procedure to table breaks when dynamic SQL is executed.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Confused with security
I thought I understood what was happening - obviously not.
Here is my problem:
Using windows integrated security.
No users should have access to the table directly.
I have a UDF that I have granted select, insert, update, and delete on to a
created roll that has windows users added to it.
The user can select through the function ok. but when they try to insert,
they get an error complaining of lack of rights to insert.
I had to add insert rights to the table for the user to insert through the
function.
Is it becuse the function is a select statement, and it dynamically creates
the update, insert methods directly against the table and by-passes the
function and that is my problem?
Thanks for any clarification.On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:
>I thought I understood what was happening - obviously not.
>Here is my problem:
>Using windows integrated security.
>No users should have access to the table directly.
>I have a UDF that I have granted select, insert, update, and delete on to a
>created roll that has windows users added to it.
>The user can select through the function ok. but when they try to insert,
>they get an error complaining of lack of rights to insert.
>I had to add insert rights to the table for the user to insert through the
>function.
>Is it becuse the function is a select statement, and it dynamically creates
>the update, insert methods directly against the table and by-passes the
>function and that is my problem?
>Thanks for any clarification.
>
Hi Howard,
Your guess is correct - welcome to the pitfalls of dynamic SQL.
If a stored procedure references a table owned by the same userid that
owns the stored procedure, no additional check for access right to that
table is made. Each user that has rights to execute the procedure can
access that table through that procedure. Other means of access to the
table still need explicit permissions for that user on the table!
Dynamic SQL is executed in a seperate environment, just as if the user
exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
that this is started from a stored procedure. Therefor, the ownership
chain from procedure to table breaks when dynamic SQL is executed.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks so much Hugo, I thought I was really lost!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:teo9j0l1fnknj0frfq3l0u6hgs2s5oh9bm@.
4ax.com...
> On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:
>
a[vbcol=seagreen]
the[vbcol=seagreen]
creates[vbcol=seagreen]
> Hi Howard,
> Your guess is correct - welcome to the pitfalls of dynamic SQL.
> If a stored procedure references a table owned by the same userid that
> owns the stored procedure, no additional check for access right to that
> table is made. Each user that has rights to execute the procedure can
> access that table through that procedure. Other means of access to the
> table still need explicit permissions for that user on the table!
> Dynamic SQL is executed in a seperate environment, just as if the user
> exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
> that this is started from a stored procedure. Therefor, the ownership
> chain from procedure to table breaks when dynamic SQL is executed.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Here is my problem:
Using windows integrated security.
No users should have access to the table directly.
I have a UDF that I have granted select, insert, update, and delete on to a
created roll that has windows users added to it.
The user can select through the function ok. but when they try to insert,
they get an error complaining of lack of rights to insert.
I had to add insert rights to the table for the user to insert through the
function.
Is it becuse the function is a select statement, and it dynamically creates
the update, insert methods directly against the table and by-passes the
function and that is my problem?
Thanks for any clarification.On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:
>I thought I understood what was happening - obviously not.
>Here is my problem:
>Using windows integrated security.
>No users should have access to the table directly.
>I have a UDF that I have granted select, insert, update, and delete on to a
>created roll that has windows users added to it.
>The user can select through the function ok. but when they try to insert,
>they get an error complaining of lack of rights to insert.
>I had to add insert rights to the table for the user to insert through the
>function.
>Is it becuse the function is a select statement, and it dynamically creates
>the update, insert methods directly against the table and by-passes the
>function and that is my problem?
>Thanks for any clarification.
>
Hi Howard,
Your guess is correct - welcome to the pitfalls of dynamic SQL.
If a stored procedure references a table owned by the same userid that
owns the stored procedure, no additional check for access right to that
table is made. Each user that has rights to execute the procedure can
access that table through that procedure. Other means of access to the
table still need explicit permissions for that user on the table!
Dynamic SQL is executed in a seperate environment, just as if the user
exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
that this is started from a stored procedure. Therefor, the ownership
chain from procedure to table breaks when dynamic SQL is executed.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks so much Hugo, I thought I was really lost!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:teo9j0l1fnknj0frfq3l0u6hgs2s5oh9bm@.
4ax.com...
> On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:
>
a[vbcol=seagreen]
the[vbcol=seagreen]
creates[vbcol=seagreen]
> Hi Howard,
> Your guess is correct - welcome to the pitfalls of dynamic SQL.
> If a stored procedure references a table owned by the same userid that
> owns the stored procedure, no additional check for access right to that
> table is made. Each user that has rights to execute the procedure can
> access that table through that procedure. Other means of access to the
> table still need explicit permissions for that user on the table!
> Dynamic SQL is executed in a seperate environment, just as if the user
> exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
> that this is started from a stored procedure. Therefor, the ownership
> chain from procedure to table breaks when dynamic SQL is executed.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)