SQL Alert: via

Showing posts with label via. Show all posts

Tuesday, March 27, 2012

accessing mapped drive via xp_cmdshell

Hello,
I'm having problems accessing a mapped network drive. Basically, I
need to be able to access the g:\ drive from SQL Server (i.e.
master..xp_cmdshell 'dir g:\')
I created a share on my server (\\MyServer\ShareName) and mapped the
network drive G to this. So, from my machine I can do a "start, run",
and type in g:\, and get what I want to see.
However, when I run:
master..xp_cmdshell 'dir g:\
I get a message "The system cannot find the path specified."
I have the MSSQLSERVER account on this server setup to login as me.
Shouldn't this take care of any issues?
I would like to use a UNC share, but unfortunately I can't do that at
this time.
Much appreciate any ideas!
SylviaTry specifying the UNC path instead of a mapped drive:
EXEC master..xp_cmdshell 'DIR \\MyServer\ShareName'
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133830852.921653.231970@.g49g2000cwa.googlegroups.com...
> Hello,
> I'm having problems accessing a mapped network drive. Basically, I
> need to be able to access the g:\ drive from SQL Server (i.e.
> master..xp_cmdshell 'dir g:\')
> I created a share on my server (\\MyServer\ShareName) and mapped the
> network drive G to this. So, from my machine I can do a "start, run",
> and type in g:\, and get what I want to see.
> However, when I run:
> master..xp_cmdshell 'dir g:\
> I get a message "The system cannot find the path specified."
> I have the MSSQLSERVER account on this server setup to login as me.
> Shouldn't this take care of any issues?
> I would like to use a UNC share, but unfortunately I can't do that at
> this time.
> Much appreciate any ideas!
> Sylvia
>|||Thanks for the reply. Unfortunately using a UNC path is not an option
at this point, as I mentioned above (some hard-coded stuff already
written, can't change right now).
Any other options? Does a network share mapped to a drive just not
work?
Thanks,
Sylvia|||> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
Sorry, I didn't understand that from your original post.
> Any other options? Does a network share mapped to a drive just not
> work?
A mapped drive can work but it is a kludge. One thing you might try is
establishing a persistent mapped drive. I expect you'll only need to do
this once, unless you change the SQL Server service account or unmap the
drive for that user.
EXEC master..xp_cmdshell 'NET USE L: \\MyServer\ShareName /PERSISTENT:YES'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <sylvia@.vasilik.com> wrote in message
news:1133845592.917117.66320@.g43g2000cwa.googlegroups.com...
> Thanks for the reply. Unfortunately using a UNC path is not an option
> at this point, as I mentioned above (some hard-coded stuff already
> written, can't change right now).
> Any other options? Does a network share mapped to a drive just not
> work?
> Thanks,
> Sylvia
>|||That did it! Thanks much - it's just the kludge I needed to get past
this.

Thursday, March 22, 2012

Accessing Custom Assemblies

We are building a report via Business Intelligent Studio. We have deployed the custom assembly dll to two folders:

1. Microsoft Sql Server\MMSQL.3\Reporting Services\Report Server\bin

2. Microsoft Visual Studio 8\Common\IDE\private assemblies.

This dll is an instance dll.

When we open the report properties/reference tab, we can find and link to the dll. Which one of the above locations is the correct one? (We have #2).

We created the class and instance entry.

Next, we added a textbox to the report layout. When we try to use the =Code keyword we get an unrecognized identifier error.

Can anyone shed any light on why this is happening. We've spent considerable time trying to find any info on this with no luck.

Any help is appreciated

Information about using custom assemblies with reports can be found here:

http://msdn2.microsoft.com/en-us/library/ms153561.aspx

For me, to use the assembly in BIDS the path is \Visual Studio 8\Common7\IDE\PrivateAssemblies

Note "Common7" and no spaces in "PrivateAssemblies".

Monday, March 19, 2012

Access Violation What does it mean?

My web app (with SSL for every page) that uses VBCOM+ components to access
SQLServer2000 SP3 via SPs had an access violation the other day when two
users were simultaneously looking at the precisely the same information on
the same customer (one was training the other using two machines and going
through each step synchronously) and I received the Access violation error
(below). This has not happened before in two years (but no one probably
every came close to simulaneous use) and these two folks were the ONLY ones
who were even using the db and what they attempted to do (a data view) works
fine after this happened. There is nothing in the SQL Server error log about
this or are there any .dmp files in LOG. I saw an old FIX re: Access
violation during SSL but I'm at SP3 already, although this clearly wasn't an
exception error.
After Googling the web, I'm not even sure what "access violation" means,
although it sounds like it has nothing to do with my T-SQL code and is more
serious.
So, what does this mean?
Thanks.
Source: Microsoft OLE DB Provider for SQL Server
Error Number: -2147217900
Description: Syntax error or access violationAn AV is generally a bug in SQL Server... Look for the error message on MS
web site (using the long number that comes with the AV), and/or open a call
to MS... IF you are identifying a bug there will be no service charge.
"Don Miller" <nospam@.nospam.com> wrote in message
news:efbjxkyQDHA.2832@.TK2MSFTNGP10.phx.gbl...
> My web app (with SSL for every page) that uses VBCOM+ components to access
> SQLServer2000 SP3 via SPs had an access violation the other day when two
> users were simultaneously looking at the precisely the same information on
> the same customer (one was training the other using two machines and going
> through each step synchronously) and I received the Access violation error
> (below). This has not happened before in two years (but no one probably
> every came close to simulaneous use) and these two folks were the ONLY
ones
> who were even using the db and what they attempted to do (a data view)
works
> fine after this happened. There is nothing in the SQL Server error log
about
> this or are there any .dmp files in LOG. I saw an old FIX re: Access
> violation during SSL but I'm at SP3 already, although this clearly wasn't
an
> exception error.
> After Googling the web, I'm not even sure what "access violation" means,
> although it sounds like it has nothing to do with my T-SQL code and is
more
> serious.
> So, what does this mean?
> Thanks.
> Source: Microsoft OLE DB Provider for SQL Server
> Error Number: -2147217900
> Description: Syntax error or access violation
>
>

Access via internet?

Can someone point me to, if its possible, a means of allowing access to an
MSDE database over the internet?
I suspect it will involve a static IP address at the host, opening a port in
the firewall, and use of NAT?
a static IP address at the host (thats when the SQL Server is reachable over
the internet in the DMZ) OR opening a port in the firewall, and use of NAT?
Thats when an option when your SQL Server is located in your LAN and
tunneled through NAT (and perhaps port translation). The sense of that would
be to use the Server for your internet application. But therefore the last
option would be preferable whereas a firewall would only allow the Web
Server (which could possibe host your application) to request data.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"mikeb" <mike@.nohostanywhere.com> schrieb im Newsbeitrag
news:eHI$yPmZFHA.1868@.TK2MSFTNGP14.phx.gbl...
> Can someone point me to, if its possible, a means of allowing access to an
> MSDE database over the internet?
> I suspect it will involve a static IP address at the host, opening a port
> in the firewall, and use of NAT?
>
|||I'm not sure I follow.
I have a PC app (actually a mobile PC app using Windows Mobile) that needs
to access a database in another factory. I was thinking of opening the
database to access via the internet (securely as possible) so that the app
could access this remote database.
I'm finding quite a few messages about people doing it, but I've still not
found how.
?
thx.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23c4KRnmZFHA.1412@.TK2MSFTNGP12.phx.gbl...
>a static IP address at the host (thats when the SQL Server is reachable
>over the internet in the DMZ) OR opening a port in the firewall, and use of
>NAT? Thats when an option when your SQL Server is located in your LAN and
>tunneled through NAT (and perhaps port translation). The sense of that
>would be to use the Server for your internet application. But therefore the
>last option would be preferable whereas a firewall would only allow the Web
>Server (which could possibe host your application) to request data.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "mikeb" <mike@.nohostanywhere.com> schrieb im Newsbeitrag
> news:eHI$yPmZFHA.1868@.TK2MSFTNGP14.phx.gbl...
>
|||I would use something more reliable than just opening a port in the
firewall, something like a VPN, that more secure than doing just a NATing.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"mikeb" <mike@.nohostanywhere.com> schrieb im Newsbeitrag
news:e0HWDXrZFHA.464@.TK2MSFTNGP15.phx.gbl...
> I'm not sure I follow.
> I have a PC app (actually a mobile PC app using Windows Mobile) that needs
> to access a database in another factory. I was thinking of opening the
> database to access via the internet (securely as possible) so that the app
> could access this remote database.
> I'm finding quite a few messages about people doing it, but I've still not
> found how.
> ?
> thx.
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:%23c4KRnmZFHA.1412@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks Jens,
Before I go down that route - I'm just curious - how unsecure is opening a
port to allow this type of access? The factory that I'm setting this up for
is small, and I'm not sure that they will afford a Windows Server to setup
VPN. I'm going to check it out with them, but I dont' think that will work.
If so, and opening a port isn't viable, we may have to resort to the old
fassioned way of using modems.
I'm very willing to continue my research - please feel free to point me
elsewhere as it sounds like maybe this topic is becoming out of scope for
this group. ?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uNyRnbrZFHA.3568@.TK2MSFTNGP10.phx.gbl...
>I would use something more reliable than just opening a port in the
>firewall, something like a VPN, that more secure than doing just a NATing.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "mikeb" <mike@.nohostanywhere.com> schrieb im Newsbeitrag
> news:e0HWDXrZFHA.464@.TK2MSFTNGP15.phx.gbl...
>
|||hi,
mikeb wrote:
> Thanks Jens,
> Before I go down that route - I'm just curious - how unsecure is
> opening a port to allow this type of access? The factory that I'm
> setting this up for is small, and I'm not sure that they will afford
> a Windows Server to setup VPN. I'm going to check it out with them,
> but I dont' think that will work. If so, and opening a port isn't
> viable, we may have to resort to the old fassioned way of using
> modems.
> I'm very willing to continue my research - please feel free to point
> me elsewhere as it sounds like maybe this topic is becoming out of
> scope for this group. ?
>
personally I'd never directly expose the database server on the Internet
that way... you are claiming for troubles :D
Jens already exposed the "classical" ways to protect you data, and doing
your way you are bypassing them all..
hope your customers will want to pay for their own security..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||My way? ack. No, really, I'm ignorant to this - I was just throwing out
my initial ideas.
So VPN is the best way to go then? Now I need to go find out some more info
on VPN.
Always open to further suggestion. or things to watch for.
I am curious, what are some of the fallbacks to using VPN for
SQLserver(msde) access? will the factory lose any other features of their
network use? I'll go check out a server or vpn group now too.
Thanks,
-m
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3g64bsFabhp0U1@.individual.net...
> hi,
> mikeb wrote:
> personally I'd never directly expose the database server on the Internet
> that way... you are claiming for troubles :D
> Jens already exposed the "classical" ways to protect you data, and doing
> your way you are bypassing them all..
> hope your customers will want to pay for their own security..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||VPN is one of the prefered ways to do this, because you dont need to open
any ports in your firewall and can grant full access to your network
(depends on how you setup the VPN). Its the securest thing with the most
variety (for me), because there are many features you can implement, like
smartcard authentication etc.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"mikeb" <mike@.nohostanywhere.com> schrieb im Newsbeitrag
news:Oc39DfsZFHA.3340@.TK2MSFTNGP10.phx.gbl...
> My way? ack. No, really, I'm ignorant to this - I was just throwing out
> my initial ideas.
> So VPN is the best way to go then? Now I need to go find out some more
> info on VPN.
> Always open to further suggestion. or things to watch for.
> I am curious, what are some of the fallbacks to using VPN for
> SQLserver(msde) access? will the factory lose any other features of their
> network use? I'll go check out a server or vpn group now too.
> Thanks,
> -m
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3g64bsFabhp0U1@.individual.net...
>
|||Personally I would NEVER expose to the Internet, a SQL Server/MSDE that I
wanted to keep secure. Don't do it, simple as that. There are better ways
to do this kind of thing - Remote Desktop over a VPN is one of them.
However, I recognise that people like breaking the rules so here's how you
do it:
1. Set your SQL Server/MSDE to use Mixed Mode Authentication (see
http://support.microsoft.com/default...;EN-US;Q325022 for details)
2. Open port 1433 on your firewall and tell your router's NAT to forward all
requests on port 1433 from the Internet to your SQL Server.
3. Sit around for a few hours while somebody brute force attacks your server
and destroys all your valuable corporate data
4. Er...that's it
I really cannot emphasis how dumb this is - IMHO the only reason that
Microsoft stuff gets hacked so often is not that it is fundamentally
insecure, it is that people do insecure things with it. Any of the recent
Microsoft Server products properly configured are as tough as the
competition.
The most secure way to expose data over the Intenet is through XML Web
Services. The great thing is that you can use the same technique to
securely manage your data internally too - definitely worth looking into.
hth
~Ben
"mikeb" <mike@.nohostanywhere.com> wrote in message
news:Oc39DfsZFHA.3340@.TK2MSFTNGP10.phx.gbl...
> My way? ack. No, really, I'm ignorant to this - I was just throwing out
> my initial ideas.
> So VPN is the best way to go then? Now I need to go find out some more
> info on VPN.
> Always open to further suggestion. or things to watch for.
> I am curious, what are some of the fallbacks to using VPN for
> SQLserver(msde) access? will the factory lose any other features of their
> network use? I'll go check out a server or vpn group now too.
> Thanks,
> -m
>

Sunday, March 11, 2012

Access to the database file is not allowed.

I'm connecting to an Sql Compact database (3.1) from a C# assembly exposed via COM and called from inside a winsock LSP. The database is only used once on instantiation of the class, which happens in the first call to WSPStartup.

The issue I'm having is that under Vista and when running inside IE7 I get the error in the title. However there is no problems with IE7 under windows XP, or with Firefox and MSN Messenger under XP or Vista!

When selecting to run IE as administrator, the problem dissapears, however when using the LogonUser API to impersonate an administrator it doesnt.

This would seem like a permissions issue, strange though that Firefox under vista has no problems.. Also, the following code throws no SecurityException under either browser, indicating the code is running with full trust.

new PermissionSet(PermissionState.Unrestricted).Demand();

Any input would be a huge help, I've been banging my head against this all day..
I can now confirm this same behaviour on VS08 Beta 2 and SqlCe 3.5

Any ideas? Anybody point me in the direction of a better place to ask as this seems more like something specific to IE7 and Vista than SqlCe as such?

Many thanks
|||To answer my own question, by default in Vista, IE runs in protected mode and is a "low integrity" process, it has even less priveleges than the logged on user, including a logged on but not elevated administrator.

Since my .sdf file was a medium integrity resource... Access Denied!

For reference, further details here.

Access to the database file is not allowed.

When selecting to run IE as administrator, the problem dissapears, however when using the LogonUser API to impersonate an administrator it doesnt.

new PermissionSet(PermissionState.Unrestricted).Demand();

Any input would be a huge help, I've been banging my head against this all day..
I can now confirm this same behaviour on VS08 Beta 2 and SqlCe 3.5

Any ideas? Anybody point me in the direction of a better place to ask as this seems more like something specific to IE7 and Vista than SqlCe as such?

Since my .sdf file was a medium integrity resource... Access Denied!

For reference, further details here.

Access to SQL Server via WCF works only part time

We have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity.

In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.

When we access Guider we have no problems getting data.

When we access Talker we have a login failure:

Cannot open database 'Talker' requested by the login. The login failed.

The thing that gets me is that the user is created at the Server level, in both Databases, and at the server level both databases are checked for the user. master has been set as the default database for the user.

Basically, as far as I can see Talker and Guider are configured identically! So I cannot figure out why I cannot login to the second database!

Is there a specific setting I'm missing somewhere to grant login access to the user? I'm using

Management Studio Express to manage the database.

My guess here is that the default database defined for the login that fails (Acorn\CommunicationServices) is not configured properly. Another possibility may be a typo when specifying the DB name in the client.

One test you can try to verify if the principal can really access the DB is:

*Connect as a sysadmin

* run “EXECUTE AS LOGIN = ‘login_name’ “ to impersonate the principal

* USE [Talker]

I hope this helps,

-Raul Garcia

SDE/T

SQL Server Engine

Access to SQL Server via WCF works only part time

We have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity.

In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.

When we access Guider we have no problems getting data.

When we access Talker we have a login failure:

Cannot open database 'Talker' requested by the login. The login failed.

Basically, as far as I can see Talker and Guider are configured identically! So I cannot figure out why I cannot login to the second database!

Is there a specific setting I'm missing somewhere to grant login access to the user? I'm using

Management Studio Express to manage the database.

One test you can try to verify if the principal can really access the DB is:

*Connect as a sysadmin

* run “EXECUTE AS LOGIN = ‘login_name’ “ to impersonate the principal

* USE [Talker]

I hope this helps,

-Raul Garcia

SDE/T

SQL Server Engine

Thursday, March 8, 2012

access to SQL from non-domain PC via Enterprise Manager

I have a developer on contract which will use her laptop to work, laptop is
not part of the domain, we install SQL tools on it and now I need to give th
at person access to one of our SQL databases, so I created sql user and then
try to register this serve
r on her laptop in Enterprise Menager but getting error “server is not run
ning or you do no have an access to it” how do bypass it? I have not pro
blem to register this server in Enterprise Manager on my PC using same sql u
ser but my PC is part of do
main, any idea?Make sure they are using TCP/IP to connect. You can force this by setting up
a client alias using the Client Network Utility. Also, try connecting using
Query Analyzer rather than EM and specifying tcp:servername in the
connection dialog to force it to use TCP/IP rather than Named pipes
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"TOM P." <TOMP@.discussions.microsoft.com> wrote in message
news:C1F92B39-7CAB-4649-8645-DBEF1D0EC2E4@.microsoft.com...
> I have a developer on contract which will use her laptop to work, laptop
is not part of the domain, we install SQL tools on it and now I need to give
that person access to one of our SQL databases, so I created sql user and
then try to register this server on her laptop in Enterprise Menager but
getting error "server is not running or you do no have an access to it"
how do bypass it? I have not problem to register this server in Enterprise
Manager on my PC using same sql user but my PC is part of domain, any idea?|||Thanks,
I have figuered it out, anyway thanks for respond... that's exactly what I h
ad to do creat Alias using TCPIP
Thanks again
"Jasper Smith" wrote:

> Make sure they are using TCP/IP to connect. You can force this by setting
up
> a client alias using the Client Network Utility. Also, try connecting usin
g
> Query Analyzer rather than EM and specifying tcp:servername in the
> connection dialog to force it to use TCP/IP rather than Named pipes
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "TOM P." <TOMP@.discussions.microsoft.com> wrote in message
> news:C1F92B39-7CAB-4649-8645-DBEF1D0EC2E4@.microsoft.com...
> is not part of the domain, we install SQL tools on it and now I need to gi
ve
> that person access to one of our SQL databases, so I created sql user and
> then try to register this server on her laptop in Enterprise Menager but
> getting error "server is not running or you do no have an access to it"
> how do bypass it? I have not problem to register this server in Enterprise
> Manager on my PC using same sql user but my PC is part of domain, any idea
?
>
>

access to SQL from non-domain PC via Enterprise Manager

I have a developer on contract which will use her laptop to work, laptop is not part of the domain, we install SQL tools on it and now I need to give that person access to one of our SQL databases, so I created sql user and then try to register this serve
r on her laptop in Enterprise Menager but getting error “server is not running or you do no have an access to it” how do bypass it? I have not problem to register this server in Enterprise Manager on my PC using same sql user but my PC is part of do
main, any idea?
Make sure they are using TCP/IP to connect. You can force this by setting up
a client alias using the Client Network Utility. Also, try connecting using
Query Analyzer rather than EM and specifying tcp:servername in the
connection dialog to force it to use TCP/IP rather than Named pipes
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"TOM P." <TOMP@.discussions.microsoft.com> wrote in message
news:C1F92B39-7CAB-4649-8645-DBEF1D0EC2E4@.microsoft.com...
> I have a developer on contract which will use her laptop to work, laptop
is not part of the domain, we install SQL tools on it and now I need to give
that person access to one of our SQL databases, so I created sql user and
then try to register this server on her laptop in Enterprise Menager but
getting error "server is not running or you do no have an access to it"
how do bypass it? I have not problem to register this server in Enterprise
Manager on my PC using same sql user but my PC is part of domain, any idea?
|||Thanks,
I have figuered it out, anyway thanks for respond... that's exactly what I had to do creat Alias using TCPIP
Thanks again
"Jasper Smith" wrote:

> Make sure they are using TCP/IP to connect. You can force this by setting up
> a client alias using the Client Network Utility. Also, try connecting using
> Query Analyzer rather than EM and specifying tcp:servername in the
> connection dialog to force it to use TCP/IP rather than Named pipes
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "TOM P." <TOMP@.discussions.microsoft.com> wrote in message
> news:C1F92B39-7CAB-4649-8645-DBEF1D0EC2E4@.microsoft.com...
> is not part of the domain, we install SQL tools on it and now I need to give
> that person access to one of our SQL databases, so I created sql user and
> then try to register this server on her laptop in Enterprise Menager but
> getting error "server is not running or you do no have an access to it"
> how do bypass it? I have not problem to register this server in Enterprise
> Manager on my PC using same sql user but my PC is part of domain, any idea?
>
>

Tuesday, March 6, 2012

Access to another database via a stored procedure

I have a stored procedure on a database that selects information from
another database, and the users running the stored procedure are not users
on the 2nd database. I don't want to have to enter all users individually
into the 2nd database, I just want anyone who has execute rights to that
stored procedure to be able to select information from the 2nd database.
Do I have to somehow in the stored procedure log into the 2nd database as a
public user?
Right now I am getting errors that says the user is not a user of the 2nd
database. The 2nd database is a public database, so I shouldn't need to be
a user on it.Users need a security context in all databases accessed. If you don't want
to add users to the 2nd database, one method:
1) enable the 'guest' user in the second database (sp_adduser 'guest')
2) enable 'db chaining on both databases (sp_dboption 'DB2', 'db chaining',
true)
If you objects are objects are owned by 'dbo', both databases need to have
the same owner so that the dbo user ownership chain is unbroken. You can
execute sp_changedbowner, if necessary. No permissions need be granted to
guest. Guest permissions are limited to those granted to public.
Note that you should fully trust those users that can create dbo-owned
objects before you enable cross-database chaining ('db chaining'). If the
databases are owned by 'sa', ensure only sy

min role members should have
permissions to create db-owned objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"et" <eagletender2001@.yahoo.com> wrote in message
news:eO3CPC1AGHA.4080@.TK2MSFTNGP14.phx.gbl...
>I have a stored procedure on a database that selects information from
>another database, and the users running the stored procedure are not users
>on the 2nd database. I don't want to have to enter all users individually
>into the 2nd database, I just want anyone who has execute rights to that
>stored procedure to be able to select information from the 2nd database. Do
>I have to somehow in the stored procedure log into the 2nd database as a
>public user?
> Right now I am getting errors that says the user is not a user of the 2nd
> database. The 2nd database is a public database, so I shouldn't need to
> be a user on it.
>|||Thanks so much, this is very helpful information. I think the guest account
will work just fine, as I agree that I don't think I want to do the cross
ownership until I see the need. Thanks.
Thanks!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OsaivR1AGHA.2512@.TK2MSFTNGP09.phx.gbl...
> Users need a security context in all databases accessed. If you don't
> want to add users to the 2nd database, one method:
> 1) enable the 'guest' user in the second database (sp_adduser 'guest')
> 2) enable 'db chaining on both databases (sp_dboption 'DB2', 'db
> chaining', true)
> If you objects are objects are owned by 'dbo', both databases need to have
> the same owner so that the dbo user ownership chain is unbroken. You can
> execute sp_changedbowner, if necessary. No permissions need be granted to
> guest. Guest permissions are limited to those granted to public.
> Note that you should fully trust those users that can create dbo-owned
> objects before you enable cross-database chaining ('db chaining'). If the
> databases are owned by 'sa', ensure only sy

min role members should have
> permissions to create db-owned objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "et" <eagletender2001@.yahoo.com> wrote in message
> news:eO3CPC1AGHA.4080@.TK2MSFTNGP14.phx.gbl...
>|||I'm glad you found the information useful.
Just to be clear, if you don't enable cross database chaining, you will then
need to grant SELECT permissions to guest (or public) in the second database
because the ownership chain is broken. This will effectively allow all
server users not already in the second database to select from the table
directly. This might be ok in your situation but I want to make sure you
are aware of the ramifications.
Hope this helps.
Dan Guzman
SQL Server MVP
"et" <eagletender2001@.yahoo.com> wrote in message
news:uh%23icI2AGHA.2788@.TK2MSFTNGP14.phx.gbl...
> Thanks so much, this is very helpful information. I think the guest
> account will work just fine, as I agree that I don't think I want to do
> the cross ownership until I see the need. Thanks.
> Thanks!
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OsaivR1AGHA.2512@.TK2MSFTNGP09.phx.gbl...
>

Saturday, February 25, 2012

Access sql server behide firewall?

How do I access a sql server behide an ISA server? What are the steps requir
ed to access via query analyzer from the internet?Have you looked at this article:
HOWTO: Configure ISA Server 2000 and Enterprise Manager to Connect Through
ISA to a SQL Server
http://support.microsoft.com/defaul...kb;en-us;299673
Rand
This posting is provided "as is" with no warranties and confers no rights.

Access sql server behide firewall?

How do I access a sql server behide an isa server? What are the steps required to access via query analyzer from the internet?
Have you looked at this article:
HOWTO: Configure ISA Server 2000 and Enterprise Manager to Connect Through
ISA to a SQL Server
http://support.microsoft.com/default...b;en-us;299673
Rand
This posting is provided "as is" with no warranties and confers no rights.

Access SQL Server 2005 via HTTP

Is it possible to access sql server 2005 via http and do some management and administration task?

Thanks in advance,

Larry

I believe that there may be a web based admin tool in development. Otherwise, your only good options are third party products or VPN.

access sql server 2005 via http

Is it possible to access sql server 2005 via http and do some management and administration task? if possible how?

Thanks in advance,

Larry

Hi larry,

I suggest you configure a windows 2000 or 2003 VPN or cisco VPN
to actually access the Sql server box via http
this is i think more secure.

You can however access sql server via SQL server Endpoint
but i suggest you do configure a VPN
since it is more secure

regards,
joey|||

http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959&displaylang=en closely related to SQL 2000 or MSDE, but you are looking for SQL 2005 instance so as suggested endpoings are good to go, http://www.developer.com/db/article.php/3390241 and http://davidhayden.com/blog/dave/archive/2006/03/31/2897.aspxfyi..

http://codebetter.com/blogs/raymond.lewallen/archive/2005/06/23/65089.aspx tooq

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.