Linked Server Properties - Security - SQL Server 2000

I would like to specify a locallogin that is a domain(not local) group. It
allows me to specify it, but does not recognize when memebrs of the group ar
e
connected. I have been forced to specify each group memebr individually. I
s
there a way to make the domain groupt option work?I do not think this is possible based on looking at the help file for
sp_addlinkedserverlogin.
Jason Massie
http://statisticsio.com
"Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>I would like to specify a locallogin that is a domain(not local) group. It
> allows me to specify it, but does not recognize when memebrs of the group
> are
> connected. I have been forced to specify each group memebr individually.
> Is
> there a way to make the domain groupt option work?|||Jason,
I agree. If I understand correctly, the logins must be either SQL Server or
Windows User logins, not Windows Groups. That is the only way the server
has a solid security context from which to work. (Much the same thing can
be seen in owners of SQL Agent jobs and several other security features.)
If the login is set up as @.useself=N'True' then anyone can use the link
under their own credentials. That may be too wide open for you Steve, but
if it is not, then you can control group membership on the other side of the
link when granting rights to the linked server's database(s).
RLF
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>I do not think this is possible based on looking at the help file for
>sp_addlinkedserverlogin.
> --
> Jason Massie
> http://statisticsio.com
> "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>|||The login connecting to my sqlserver has no credentials on the remote server
.
My server is an integration point for several databases. My application has
credentials on the remote servers, but If I need to get to my server with sa
y
a firecall id, I want to be able to access the remove servers with the same
permissions that my app has. Our firecall ids are all in a network group.
There are about 60 ids. Fortunatley, they do not change very often, so I
incorporated showmbrs.exe into my script, and add each id individually.
However, if the group changes, I will need to re-run the script. Was lookin
g
for a better option.
"Russell Fields" wrote:

> Jason,
> I agree. If I understand correctly, the logins must be either SQL Server o
r
> Windows User logins, not Windows Groups. That is the only way the server
> has a solid security context from which to work. (Much the same thing can
> be seen in owners of SQL Agent jobs and several other security features.)
> If the login is set up as @.useself=N'True' then anyone can use the link
> under their own credentials. That may be too wide open for you Steve, but
> if it is not, then you can control group membership on the other side of t
he
> link when granting rights to the linked server's database(s).
> RLF
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>
>

Linked Server Properties - Security - SQL Server 2000

I would like to specify a locallogin that is a domain(not local) group. It
allows me to specify it, but does not recognize when memebrs of the group are
connected. I have been forced to specify each group memebr individually. Is
there a way to make the domain groupt option work?
I do not think this is possible based on looking at the help file for
sp_addlinkedserverlogin.
Jason Massie
http://statisticsio.com
"Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>I would like to specify a locallogin that is a domain(not local) group. It
> allows me to specify it, but does not recognize when memebrs of the group
> are
> connected. I have been forced to specify each group memebr individually.
> Is
> there a way to make the domain groupt option work?
|||Jason,
I agree. If I understand correctly, the logins must be either SQL Server or
Windows User logins, not Windows Groups. That is the only way the server
has a solid security context from which to work. (Much the same thing can
be seen in owners of SQL Agent jobs and several other security features.)
If the login is set up as @.useself=N'True' then anyone can use the link
under their own credentials. That may be too wide open for you Steve, but
if it is not, then you can control group membership on the other side of the
link when granting rights to the linked server's database(s).
RLF
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>I do not think this is possible based on looking at the help file for
>sp_addlinkedserverlogin.
> --
> Jason Massie
> http://statisticsio.com
> "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>
|||The login connecting to my sqlserver has no credentials on the remote server.
My server is an integration point for several databases. My application has
credentials on the remote servers, but If I need to get to my server with say
a firecall id, I want to be able to access the remove servers with the same
permissions that my app has. Our firecall ids are all in a network group.
There are about 60 ids. Fortunatley, they do not change very often, so I
incorporated showmbrs.exe into my script, and add each id individually.
However, if the group changes, I will need to re-run the script. Was looking
for a better option.
"Russell Fields" wrote:

> Jason,
> I agree. If I understand correctly, the logins must be either SQL Server or
> Windows User logins, not Windows Groups. That is the only way the server
> has a solid security context from which to work. (Much the same thing can
> be seen in owners of SQL Agent jobs and several other security features.)
> If the login is set up as @.useself=N'True' then anyone can use the link
> under their own credentials. That may be too wide open for you Steve, but
> if it is not, then you can control group membership on the other side of the
> link when granting rights to the linked server's database(s).
> RLF
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>
>

Linked Server Properties - Security - SQL Server 2000

I would like to specify a locallogin that is a domain(not local) group. It
allows me to specify it, but does not recognize when memebrs of the group are
connected. I have been forced to specify each group memebr individually. Is
there a way to make the domain groupt option work?I do not think this is possible based on looking at the help file for
sp_addlinkedserverlogin.
--
Jason Massie
http://statisticsio.com
"Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>I would like to specify a locallogin that is a domain(not local) group. It
> allows me to specify it, but does not recognize when memebrs of the group
> are
> connected. I have been forced to specify each group memebr individually.
> Is
> there a way to make the domain groupt option work?|||Jason,
I agree. If I understand correctly, the logins must be either SQL Server or
Windows User logins, not Windows Groups. That is the only way the server
has a solid security context from which to work. (Much the same thing can
be seen in owners of SQL Agent jobs and several other security features.)
If the login is set up as @.useself=N'True' then anyone can use the link
under their own credentials. That may be too wide open for you Steve, but
if it is not, then you can control group membership on the other side of the
link when granting rights to the linked server's database(s).
RLF
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>I do not think this is possible based on looking at the help file for
>sp_addlinkedserverlogin.
> --
> Jason Massie
> http://statisticsio.com
> "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>>I would like to specify a locallogin that is a domain(not local) group.
>>It
>> allows me to specify it, but does not recognize when memebrs of the group
>> are
>> connected. I have been forced to specify each group memebr individually.
>> Is
>> there a way to make the domain groupt option work?
>|||The login connecting to my sqlserver has no credentials on the remote server.
My server is an integration point for several databases. My application has
credentials on the remote servers, but If I need to get to my server with say
a firecall id, I want to be able to access the remove servers with the same
permissions that my app has. Our firecall ids are all in a network group.
There are about 60 ids. Fortunatley, they do not change very often, so I
incorporated showmbrs.exe into my script, and add each id individually.
However, if the group changes, I will need to re-run the script. Was looking
for a better option.
"Russell Fields" wrote:
> Jason,
> I agree. If I understand correctly, the logins must be either SQL Server or
> Windows User logins, not Windows Groups. That is the only way the server
> has a solid security context from which to work. (Much the same thing can
> be seen in owners of SQL Agent jobs and several other security features.)
> If the login is set up as @.useself=N'True' then anyone can use the link
> under their own credentials. That may be too wide open for you Steve, but
> if it is not, then you can control group membership on the other side of the
> link when granting rights to the linked server's database(s).
> RLF
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
> >I do not think this is possible based on looking at the help file for
> >sp_addlinkedserverlogin.
> >
> > --
> > Jason Massie
> > http://statisticsio.com
> >
> > "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> > message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
> >>I would like to specify a locallogin that is a domain(not local) group.
> >>It
> >> allows me to specify it, but does not recognize when memebrs of the group
> >> are
> >> connected. I have been forced to specify each group memebr individually.
> >> Is
> >> there a way to make the domain groupt option work?
> >
>
>

LINKED SERVER NESTED OPENQUERY SAMPLE FROM TIPS AND TRICKS DOESN'T APPEARS TO FAIL WITH MSO

Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.

I'm generally following theTips and Tricks article.

"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.

e.g.

SELECT * FROM OPENQUERY(DMServer,

'select … FROM Modell PREDICTION JOIN OPENQUERY…')

The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".

Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query

SELECT * FROM OPENQUERY(DMServer,

'SELECT

t.[CardTransactionID],

t.[PostingDate],

[Misuse Abuse Profile].[Even Dollar Purchase],

PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score,

PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt,

t.[BillingAmount]

FROM

[Misuse Abuse Profile]

PREDICTION JOIN

OPENQUERY([Athena Dev],

''SELECT

[CardTransactionID],

[PostingDate],

[BillingAmount],

[AccountNumber],

[SupplierStateProvinceCode],

[MerchantCategoryCode],

[PurchaseIDFormat],

[TransactionTime],

[TaxAmountIncludedCode],

[Tax2AmountIncludedCode],

[OrderTypeCode],

[MemoPostFlag],

[EvenDollarPurchase]

FROM

[dbo].[vMisuseAbuseProfile]

'') AS t

ON

[Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND

[Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND

[Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND

[Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND

[Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND

[Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND

[Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND

[Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND

[Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND

[Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase]

')

In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no?

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".

It appears the default Analysis Services Data Source wizard selection of SQL Native Client Data Source provider cannot prepare an OPENQUERY if passed from a linked server? I changed the Datasource to Provider=SQLOLEDB.1 and the query now works.

|||this is very odd - we will look into it - thanks for reporting.|||

Hi Bill,

Could you give more details about the setup. Is the SQL server where you defined the linked server DMTest on the same machine with the Analysis Server it refers too? And also is the SQL server refered on the inner openquery datasource [Athena Dev] on a different machine than the machines with AS and SQL server with added linked server?

Thanks,

Dana Cristofor

Linked Server Alias?

Is it possible to create an alias for a linked server? For instance when I
reference a linked server today I have to specify
server.database.schema.table. I would like to be able to create an alias
for the linked server so I could just reference it as alias.table."Dave" <dave@.aol.com> wrote in message news:mZYQg.219$8O1.88@.trnddc04...
> Is it possible to create an alias for a linked server? For instance when
> I reference a linked server today I have to specify
> server.database.schema.table. I would like to be able to create an alias
> for the linked server so I could just reference it as alias.table.
>
In 2005, you can accomplish this with schemas and synonyms.
EG
create schema SalesDB
go
create synonym SalesDB.MyTable
for [SALESPROD09].[SalesMaster].Dbo.MyTable
David