Access AS from Excel 2007 from outside the domain

I am trying to access an AS DB from outside the domain. I'm using the https://.../msmdpump.dll to specify the server name. Is there a way to qualify the specific AS DB in the supplied server name? Excel allows for both Windows authentication as well as providing username and password, which seems to be a mistake - it appears it uses the same dialog box in creating a connection to AS and DB, so provides the ability to pass l/p. Can I provide a valid Domain login in these fields for connecting to AS? My machine is of course in a different domain then AS - is there a way to pass the remote domains credentials from this machine?

Thanks

If the laptop conaining Excel 2007 is not on the same domain as SSAS or the msmdpump.dll web server, then I would suggest one of two options:

1. Launch Excel with the runas command:

Code Block

C:\WINDOWS\system32\runas.exe /net /user:SSASDomain\username "C:\program files\Microsoft Office\Office12\EXCEL.EXE"

Then connect with "Use Windows Authentication" in Excel.

2. If that's not an acceptable solution, then enable basic security on the msmdpump virtual directory (which is still secure because you're using HTTPS) and connect using "Use the following User Name and Password" in Excel. Note that their password will be stored in the xlsx in clear text and on disk elsewhere on their computer. And if these users can publish to Excel Services, there will be a bunch of other complexities to worry about. (If Excel Services publishing is needed for these users, I would suggest you have them submit their xlsx files to internal IT who can remove the basic security and flip the connections over to Integrated Security before publishing to Excel Services.)

If msmdpump.dll is not on the same server as SSAS, you may have troubles getting #1 to work since Kerberos delegation across domains starting with a connection in Excel isn't possible that I know of. But #2 should work.

As for specifying the database, I believe that is configured on the next screen in the connection wizard in Excel 2007. If you meant "can msmdpump.dll point to several servers?" I think the answer is no. There's a msmdpump.ini which controls which SSAS server it connects to. But you can certainly deploy multiple msmdpump.dll virtual directories.

Keep us posted with your progress or with other lessons learned if you run into some gotchas. Hope the above helps.

About extended stored procedures

Hi,
I have such situation:
A function that make some custom translation of numbers (convert numbers to
text). I have this function in DLL. I want to use that function in my
selects like that:
SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
FROM SOME_TABLE
What are my options? Extended stored procedure? Something else? I know that
Extended stored procedures are function in external DLL that meet some
requirements, but can I use Extended stored procedure in such way?
TIA,
Miro.Hi Miro.
You can't call an xp_ directly in a set based call like that.
To do a set based call against an external library, you'd wrap the library
in a udf(), but calls to the xp_ will be serialized so performance might not
be too thrilling, depending on the number of rows in the select & the amount
of work / efficiency of the xp_.
If the custom logic isn't too complex, you might consider consider
converting to a t-sql udf() as this would give you the best performance, if
that's important to you..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||There are only extended stored procedures and you would need to use EXEC to
call them. This sort of syntax is currently not supported for extended
stored procedures. Even if you call extended stored procedures from
functions, you have many restrictions there.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||For now you should use a UDF. When YUKON comes out, you can make your UDF in
ainy dot net language... today your UDF must be written in T-SQL...
Another thing you might consider is writing a com object then using
sp_oacreate in a function to invoke it, but performance ( I suspect) would
not be as good as if you had written the UDF directly ONLY using T-SQL
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...
> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers
to
> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know
that
> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.|||On Tue, 13 Jan 2004 21:55:34 +1100
"Greg Linwood" <g_linwoodQhotmail.com> wrote:
> Hi Miro.
> You can't call an xp_ directly in a set based call like that.
> To do a set based call against an external library, you'd wrap the library
> in a udf(), but calls to the xp_ will be serialized so performance might not
> be too thrilling, depending on the number of rows in the select & the amount
> of work / efficiency of the xp_.
> If the custom logic isn't too complex, you might consider consider
> converting to a t-sql udf() as this would give you the best performance, if
> that's important to you..
The performance is not important in my case because that function will be used in SELECTs with small number of rows in result set (in most cases with only one row in result set). And the logic is complex enough to have that logic in two sources - one in SQL and one in my source. So I will made it with xp_.
Thanks again for your help,
Miro.|||On Tue, 13 Jan 2004 06:24:04 -0500
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote:
> For now you should use a UDF. When YUKON comes out, you can make your UDF in
> ainy dot net language...
:) Ooo god - no! The 'dot net' is the BIG mistake in world of programming for last 20 years ;).
> today your UDF must be written in T-SQL...
> Another thing you might consider is writing a com object then using
> sp_oacreate in a function to invoke it, but performance ( I suspect) would
> not be as good as if you had written the UDF directly ONLY using T-SQL
I prefer to do it in xp_ and make wrapper for it in UDF (see my other post in that thread). Logic inside is too complex to have two sources to maintain - one in T_SQL and one in my source (Delphi source actually).
10x,
Miro.

About extended stored procedures

Hi,
I have such situation:
A function that make some custom translation of numbers (convert numbers to
text). I have this function in DLL. I want to use that function in my
selects like that:
SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
FROM SOME_TABLE
What are my options? Extended stored procedure? Something else? I know that
Extended stored procedures are function in external DLL that meet some
requirements, but can I use Extended stored procedure in such way?
TIA,
Miro.Hi Miro.
You can't call an xp_ directly in a set based call like that.
To do a set based call against an external library, you'd wrap the library
in a udf(), but calls to the xp_ will be serialized so performance might not
be too thrilling, depending on the number of rows in the select & the amount
of work / efficiency of the xp_.
If the custom logic isn't too complex, you might consider consider
converting to a t-sql udf() as this would give you the best performance, if
that's important to you..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...

quote:

---

> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers

---

to

quote:

---

> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know

---

that

quote:

---

> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.

---

|||There are only extended stored procedures and you would need to use EXEC to
call them. This sort of syntax is currently not supported for extended
stored procedures. Even if you call extended stored procedures from
functions, you have many restrictions there.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...

quote:

---

> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers

---

to

quote:

---

> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know

---

that

quote:

---

> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.

---

|||For now you should use a UDF. When YUKON comes out, you can make your UDF in
ainy dot net language... today your UDF must be written in T-SQL...
Another thing you might consider is writing a com object then using
sp_oacreate in a function to invoke it, but performance ( I suspect) would
not be as good as if you had written the UDF directly ONLY using T-SQL
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Miro Penchev" <miroslav.penchev@.bsc.bg> wrote in message
news:opr1pl4riy12pphn@.msnews.microsoft.com...

quote:

---

> Hi,
> I have such situation:
> A function that make some custom translation of numbers (convert numbers

---

to

quote:

---

> text). I have this function in DLL. I want to use that function in my
> selects like that:
> SELECT SOME_NUMBER, xp_transfunc(SOME_NUMBER) AS SOME_NUMBER_AS_TEXT
> FROM SOME_TABLE
> What are my options? Extended stored procedure? Something else? I know

---

that

quote:

---

> Extended stored procedures are function in external DLL that meet some
> requirements, but can I use Extended stored procedure in such way?
> TIA,
> Miro.

---

|||On Tue, 13 Jan 2004 21:55:34 +1100
"Greg Linwood" <g_linwoodQhotmail.com> wrote:

quote:

---

> Hi Miro.
> You can't call an xp_ directly in a set based call like that.
> To do a set based call against an external library, you'd wrap the library
> in a udf(), but calls to the xp_ will be serialized so performance might n
ot
> be too thrilling, depending on the number of rows in the select & the amou
nt
> of work / efficiency of the xp_.
> If the custom logic isn't too complex, you might consider consider
> converting to a t-sql udf() as this would give you the best performance, i
f
> that's important to you..

---

The performance is not important in my case because that function will be us
ed in SELECTs with small number of rows in result set (in most cases with on
ly one row in result set). And the logic is complex enough to have that logi
c in two sources - one in S
QL and one in my source. So I will made it with xp_.
Thanks again for your help,
Miro.|||On Tue, 13 Jan 2004 06:24:04 -0500
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote:

quote:

---

> For now you should use a UDF. When YUKON comes out, you can make your UDF
in
> ainy dot net language...

---

Ooo god - no! The 'dot net' is the BIG mistake in world of programming fo
r last 20 years ;).

quote:

---

> today your UDF must be written in T-SQL...
> Another thing you might consider is writing a com object then using
> sp_oacreate in a function to invoke it, but performance ( I suspect) would
> not be as good as if you had written the UDF directly ONLY using T-SQL

---

I prefer to do it in xp_ and make wrapper for it in UDF (see my other post i
n that thread). Logic inside is too complex to have two sources to maintain
- one in T_SQL and one in my source (Delphi source actually).
10x,
Miro.