|
Home > Archive > MS SQL Server OLAP > December 2005 > Connecting to AS 2005 using a specified user
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Connecting to AS 2005 using a specified user
|
|
| Gustaf 2005-12-01, 11:23 am |
| in AS 2005 it was not possible to state a user and password in order to
connect to a cube. All end user access was controlled by a windows accont. Is
it possible to state a username and password in the connectionstring using
msolap.3???
Does AS 2005 support connecting via HTTP in the same manner as SQL server
2000?
Regards Gustaf
| |
|
| No AS2005 http connectivity won't work in your case. You have to stay with
AS2000.
Have a look at the thread "MDX Sample App broken by 2005" in this newsgroup.
"Gustaf" wrote:
> in AS 2005 it was not possible to state a user and password in order to
> connect to a cube. All end user access was controlled by a windows accont. Is
> it possible to state a username and password in the connectionstring using
> msolap.3???
>
> Does AS 2005 support connecting via HTTP in the same manner as SQL server
> 2000?
>
> Regards Gustaf
>
| |
| Gustaf 2005-12-02, 9:23 am |
| Ok. thanx pat.
Any idea on how to connect if a user does not logon to the same domain as
the SQL server?
Regards Gustaf
"Pat" wrote:
[color=darkred]
> No AS2005 http connectivity won't work in your case. You have to stay with
> AS2000.
> Have a look at the thread "MDX Sample App broken by 2005" in this newsgroup.
>
> "Gustaf" wrote:
>
| |
|
| You need to set up a windows account for each user on each AS2005 server and
connect using the SERVERNAME:PORT syntax in the data source, after assigning
a fixed port to AS2005.
You also need to install oledb 9 at each client, and this will cause their
existing http access to AS2000 to fail, since my experience is that oledb 9
is not backwards-compatible with oledb 8.
Basically, AS2005 connectivity only works if you're a Microsoft-only shop.
Otherwise, you've got to stick to AS2000
"Gustaf" wrote:
[color=darkred]
> Ok. thanx pat.
>
> Any idea on how to connect if a user does not logon to the same domain as
> the SQL server?
>
> Regards Gustaf
>
> "Pat" wrote:
>
| |
| Darren Gosbell 2005-12-02, 8:24 pm |
| AS2005 does allow a similar HTTP connection option to AS2000.
Actually the "MDX Sample App broken by 2005" thread only referred to an
issue where the person was using basic authentication with prompt=1
turned on and the end users were being asked to authenticate twice. HTTP
authentication does still work in 2005, the issue was the users had to
enter their username and password twice.
If you want to put the userid and password in the connection string you
should not see this issue.
Check out:
http://www.microsoft.com/technet/pr...5/httpasws.mspx
....for configuring HTTP access on Win 2003
And:
http://www.microsoft.com/technet/pr...5/httpssas.mspx
....for configuring HTTP access on Win XP
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <0ECC73AD-25D1-4AC9-9B08- 5920169F50F8@microso
ft.com>,
pat@online.nospam says...
> You need to set up a windows account for each user on each AS2005 server and
> connect using the SERVERNAME:PORT syntax in the data source, after assigning
> a fixed port to AS2005.
> You also need to install oledb 9 at each client, and this will cause their
> existing http access to AS2000 to fail, since my experience is that oledb 9
> is not backwards-compatible with oledb 8.
>
> Basically, AS2005 connectivity only works if you're a Microsoft-only shop.
> Otherwise, you've got to stick to AS2000
>
>
> "Gustaf" wrote:
>
>
| |
|
| I have opened a support call with PSS and after 2 weeks of hard work, the
conclusion of the escalation engineer is that you cannot use http and basic
authentication on AS2005, which worked on AS2000.
People on this newsgroup tend to be connected to the server through a
Windows network and use windows authentication (or have the same user on the
client and the server, which is not workable in a production environment).
The problem is that the new OLEDB 9 driver prevents the authentication
information to be passed from IIS to AS2005, so you have no way to
authenticate to AS2005, unless you connect directly to it the way I indicated.
The next problem is that once you have installed OLEDB 9 on your clients
machines, they cannot access AS2000 through http any more in the following
scenario: connect to an AS2000 cube with OWC, click "Export to Excel", then
try to refresh in Excel. This fails with OLEDB 9, works with OLEDB 8. So
their existing application is broken.
Basically, you don't want to go for AS2005 if you don't have all of your
users and the server in one single Windows domain, or in trusted domains.
| |
| Darren Gosbell 2005-12-04, 7:23 am |
| Whoa... thanks for sharing that with everyone. I think you are right in
that most people on this group are working with a windows based network
infrastructure. I suppose all we can do is to keep an eye on the service
pack releases and see if anything happens on this front.
> The next problem is that once you have installed OLEDB 9 on your clients
> machines, they cannot access AS2000 through http any more in the following
> scenario: connect to an AS2000 cube with OWC, click "Export to Excel", then
> try to refresh in Excel. This fails with OLEDB 9, works with OLEDB 8. So
> their existing application is broken.
This is probably because the export to excel probably just specifies to
use the MSOLAP provider, which will default to the latest version -
MSOLAP.3 (the 2005 version) instead of MSOLAP.2 (the 2000 version).
You probably don't have any nice solutions here either. You could try
re-registering msolap80.dll to try and get it to set itself as the
default provider. Even if you uninstall the OLEDB 9 driver you may find
you need to re-register the 8 driver - I don't know how clean the
uninstall is. The only other approach I can think of which would be a
bit riskier would be to alter the registry to reset the default provider
to the version 8 driver.
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <A5950AD0-DE8B-4EC6-8113- E0AE8567A608@microso
ft.com>,
pat@online.nospam says...
> I have opened a support call with PSS and after 2 weeks of hard work, the
> conclusion of the escalation engineer is that you cannot use http and basic
> authentication on AS2005, which worked on AS2000.
> People on this newsgroup tend to be connected to the server through a
> Windows network and use windows authentication (or have the same user on the
> client and the server, which is not workable in a production environment).
>
> The problem is that the new OLEDB 9 driver prevents the authentication
> information to be passed from IIS to AS2005, so you have no way to
> authenticate to AS2005, unless you connect directly to it the way I indicated.
>
> The next problem is that once you have installed OLEDB 9 on your clients
> machines, they cannot access AS2000 through http any more in the following
> scenario: connect to an AS2000 cube with OWC, click "Export to Excel", then
> try to refresh in Excel. This fails with OLEDB 9, works with OLEDB 8. So
> their existing application is broken.
>
> Basically, you don't want to go for AS2005 if you don't have all of your
> users and the server in one single Windows domain, or in trusted domains.
>
>
| |
|
| Not sure what you mean in your first paragraph. Do you find it good that
Microsoft comes up with a product upgrade that shuts off anybody not on a
Microsoft network from using the product?
I note that you aknowledge that people cannot concurrently work on AS2000
and AS2005 servers. You can't ask users to fiddle with the registry every
time they use a different server.
How many companies do you have out there that will flick a big switch one
morning and suddenly replace all AS2000 applications by AS2005 applications?
Not one.
"Darren Gosbell" wrote:
> Whoa... thanks for sharing that with everyone. I think you are right in
> that most people on this group are working with a windows based network
> infrastructure. I suppose all we can do is to keep an eye on the service
> pack releases and see if anything happens on this front.
>
>
> This is probably because the export to excel probably just specifies to
> use the MSOLAP provider, which will default to the latest version -
> MSOLAP.3 (the 2005 version) instead of MSOLAP.2 (the 2000 version).
>
> You probably don't have any nice solutions here either. You could try
> re-registering msolap80.dll to try and get it to set itself as the
> default provider. Even if you uninstall the OLEDB 9 driver you may find
> you need to re-register the 8 driver - I don't know how clean the
> uninstall is. The only other approach I can think of which would be a
> bit riskier would be to alter the registry to reset the default provider
> to the version 8 driver.
>
> --
> Regards
> Darren Gosbell [MCSD]
> Blog: http://www.geekswithblogs.net/darrengosbell
>
> In article <A5950AD0-DE8B-4EC6-8113- E0AE8567A608@microso
ft.com>,
> pat@online.nospam says...
>
| |
| Darren Gosbell 2005-12-05, 7:23 am |
| > Not sure what you mean in your first paragraph. Do you find it good that
> Microsoft comes up with a product upgrade that shuts off anybody not on a
> Microsoft network from using the product?
Sorry.. It's hard to convey tone of voice in a newsgroup post :)
I think it is a large oversight on behalf of MS not to have made sure
that the HTTP connectivity would have worked at least the same as it did
in AS2k.
> I note that you aknowledge that people cannot concurrently work on AS2000
> and AS2005 servers. You can't ask users to fiddle with the registry every
> time they use a different server.
>
I am hoping that with an admin deployed registry file you might be able
to switch the default OLAP provider back to the 8.0 version so that http
access and "open in excel" from OWC still work.
If you are interested in trying to come up with a solution that can get
the 9.0 and 8.0 drivers co-existing, I am happy to help if I can. Of
course in your situation I would not be surprised if you just wanted to
back out the 2005 version completely.
> How many companies do you have out there that will flick a big switch one
> morning and suddenly replace all AS2000 applications by AS2005 applications?
> Not one.
Not one, but there could be some that spend a significant amount of
funds on an upgrade, expecting http connectivity to work as it did in
AS2k only to hit this issue.
So once again - thanks for sharing your hard won knowledge. I hope that
others might benefit from this and also that MS might fix it in the near
future.
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <3E568801-8C65-472E-9DE4- 2F2E536CA9CE@microso
ft.com>,
pat@online.nospam says...[color=darkred]
>
>
>
>
> "Darren Gosbell" wrote:
>
| |
| Dave Wickert [MSFT] 2005-12-05, 8:25 pm |
| Do you have a case# for this incident? I'd like to look it up.
This is certainly is not our intention for this feature.
It is a bug and I want to make sure it is being treated as such internally.
You can just email it to me directly.
--
Dave Wickert [MSFT]
dwickert@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pat" <pat@online.nospam> wrote in message
news:A5950AD0-DE8B-4EC6-8113- E0AE8567A608@microso
ft.com...
>I have opened a support call with PSS and after 2 weeks of hard work, the
> conclusion of the escalation engineer is that you cannot use http and
> basic
> authentication on AS2005, which worked on AS2000.
> People on this newsgroup tend to be connected to the server through a
> Windows network and use windows authentication (or have the same user on
> the
> client and the server, which is not workable in a production environment).
>
> The problem is that the new OLEDB 9 driver prevents the authentication
> information to be passed from IIS to AS2005, so you have no way to
> authenticate to AS2005, unless you connect directly to it the way I
> indicated.
>
> The next problem is that once you have installed OLEDB 9 on your clients
> machines, they cannot access AS2000 through http any more in the following
> scenario: connect to an AS2000 cube with OWC, click "Export to Excel",
> then
> try to refresh in Excel. This fails with OLEDB 9, works with OLEDB 8. So
> their existing application is broken.
>
> Basically, you don't want to go for AS2005 if you don't have all of your
> users and the server in one single Windows domain, or in trusted domains.
>
| |
| Gustaf 2005-12-06, 3:23 am |
| I've been following your interesting discussion in awe and horror ;-)
I appreciate all your information, but the conclusion is not very appealing.
The system we’ve been building aims at an environment where all clients
reside in a windows active directory, however this will be a reality in about
3-6 months. Our conviction was that we could use the HTTP connectivity until
this day… Right now we’re using Novel client logon.
I’d like to know more about your connect through a given port, Pat. Would
this work in an environment with Novel client logons? Do you have any
document describing this approach?
"Darren Gosbell" wrote:
>
> Sorry.. It's hard to convey tone of voice in a newsgroup post :)
>
> I think it is a large oversight on behalf of MS not to have made sure
> that the HTTP connectivity would have worked at least the same as it did
> in AS2k.
>
>
> I am hoping that with an admin deployed registry file you might be able
> to switch the default OLAP provider back to the 8.0 version so that http
> access and "open in excel" from OWC still work.
>
> If you are interested in trying to come up with a solution that can get
> the 9.0 and 8.0 drivers co-existing, I am happy to help if I can. Of
> course in your situation I would not be surprised if you just wanted to
> back out the 2005 version completely.
>
>
> Not one, but there could be some that spend a significant amount of
> funds on an upgrade, expecting http connectivity to work as it did in
> AS2k only to hit this issue.
>
> So once again - thanks for sharing your hard won knowledge. I hope that
> others might benefit from this and also that MS might fix it in the near
> future.
>
> --
> Regards
> Darren Gosbell [MCSD]
> Blog: http://www.geekswithblogs.net/darrengosbell
>
> In article <3E568801-8C65-472E-9DE4- 2F2E536CA9CE@microso
ft.com>,
> pat@online.nospam says...
>
| |
| Gustaf 2005-12-06, 3:23 am |
| I'm very interesing in following this issue, please post any details to this
newsgrup.
Regards Gustaf
"Dave Wickert [MSFT]" wrote:
> Do you have a case# for this incident? I'd like to look it up.
> This is certainly is not our intention for this feature.
> It is a bug and I want to make sure it is being treated as such internally.
> You can just email it to me directly.
> --
> Dave Wickert [MSFT]
> dwickert@online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Pat" <pat@online.nospam> wrote in message
> news:A5950AD0-DE8B-4EC6-8113- E0AE8567A608@microso
ft.com...
>
>
>
| |
| Darren Gosbell 2005-12-06, 3:23 am |
| From memory the Novel client allows for the specification of a local
machine login. We used this at one client and set up matching local
users on the AS server. I believe this strategy still works, it is not
ideal and quickly becomes an admin nightmare as the number of users
grow. But if you don't have too many users (at least in the first 3-6
mths) it might get you up and running.
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <E3424C02-69D1-42FD-ABA3- F4B3DBEFE5A7@microso
ft.com>,
Gustaf@discussions.microsoft.com says...[color=darkred]
> I've been following your interesting discussion in awe and horror ;-)
>
> I appreciate all your information, but the conclusion is not very appealing.
> The system we�ve been building aims at an environment where all clients
> reside in a windows active directory, however this will be a reality in about
> 3-6 months. Our conviction was that we could use the HTTP connectivity until
> this day� Right now we�re using Novel client logon.
>
> I�d like to know more about your connect through a given port, Pat. Would
> this work in an environment with Novel client logons? Do you have any
> document describing this approach?
>
>
> "Darren Gosbell" wrote:
>
| |
|
| I won't post details on the newsgroup, but I will definitely post the
solution / workaround if there is one.
I'm in the same situation as you are, developed a nice AS2005 solution, and
users cannot use it. That's why I'm pretty ennoyed. Except that we're not
migrating to active directory, we'll be staying with Novell. We're a big
company where we've just started to use MS solutions a couple of years ago,
no way the network will be re-done just for our project.
"Gustaf" wrote:
[color=darkred]
> I'm very interesing in following this issue, please post any details to this
> newsgrup.
>
> Regards Gustaf
>
> "Dave Wickert [MSFT]" wrote:
>
| |
|
| Sorry, forgot the question about direct connection.
Just specify SERVER:PORT where you used to specify http://SERVER/ in the
connection strings.
To assign AS2005 to a specific port, right click on the AS server in the
management studio, choose Properties, select Show Advanced Properties, and
set the value in the Port property.
"Gustaf" wrote:
[color=darkred]
> I'm very interesing in following this issue, please post any details to this
> newsgrup.
>
> Regards Gustaf
>
> "Dave Wickert [MSFT]" wrote:
>
| |
| Dave Wickert [MSFT] 2005-12-07, 8:25 pm |
| Specifying the port number like that will control the http port which will
be used (rather than the default 80). If you want to change the AS port
number, you need to edit the config file for the data pump.
--
Dave Wickert [MSFT]
dwickert@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pat" <pat@online.nospam> wrote in message
news:F68ED036-322E-4E71-A850- 493DAA5AD56D@microso
ft.com...[color=darkred]
> Sorry, forgot the question about direct connection.
> Just specify SERVER:PORT where you used to specify http://SERVER/ in the
> connection strings.
> To assign AS2005 to a specific port, right click on the AS server in the
> management studio, choose Properties, select Show Advanced Properties, and
> set the value in the Port property.
>
> "Gustaf" wrote:
>
| |
| John Lee 2005-12-11, 8:24 pm |
| I'd like to know which one among Pat's two problems is a bug. Basic auth
issue or OLE 9 driver issue?
Regards,
From John Lee
"Dave Wickert [MSFT]" wrote:
> Do you have a case# for this incident? I'd like to look it up.
> This is certainly is not our intention for this feature.
> It is a bug and I want to make sure it is being treated as such internally.
> You can just email it to me directly.
> --
> Dave Wickert [MSFT]
> dwickert@online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Pat" <pat@online.nospam> wrote in message
> news:A5950AD0-DE8B-4EC6-8113- E0AE8567A608@microso
ft.com...
>
>
>
| |
| Akshai Mirchandani [MS] 2005-12-14, 8:25 pm |
| Just to clarify -- as far as I know there are currently only two issues
related to Basic authentication and HTTP on AS 2005:
1. If you use MSOLAP.3 to connect to an AS 2000 server over HTTP, it will
not pass the userid/password correctly. This can be easily worked around by
specifying Provider=MSOLAP.2 on the connection string of your application.
2. If you are inside Internet Explorer and browse to a web page and specify
Basic authentication credentials. Then the web page inside IE tries to
connect to an AS server over HTTP in the same "realm" -- for AS 2000, this
would work without demanding the basic authentication credentials again. But
in AS 2005, you are required to re-submit the credentials a second time.
There is a good technical explanation for this which I won't go into, but
the AS 2000 way was a little unsafe and it's probably a good thing that this
doesn't work today...
Other than these two issues, is there something else that is blocking you?
Because in general Basic authentication over HTTP should work against AS
2005 and I'm not aware of any further issues there...
I'm also missing some context in the statement below -- could you please
expand on it?
> The problem is that the new OLEDB 9 driver prevents the authentication
> information to be passed from IIS to AS2005, so you have no way to
> authenticate to AS2005, unless you connect directly to it the way I
> indicated.
Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"John Lee" <John Lee@discussions.microsoft.com> wrote in message
news:41F30399-D2CC-4B8A-BDF9- DC4587AE67A8@microso
ft.com...[color=darkred]
> I'd like to know which one among Pat's two problems is a bug. Basic auth
> issue or OLE 9 driver issue?
>
> Regards,
> From John Lee
>
> "Dave Wickert [MSFT]" wrote:
>
| |
|
| Hi Akshai,
Your point 1: specifying MSOLAP.2 in the connection string has no effect
when connecting from Excel. You need to change the HKCR\MSOLAP keys in the
registry.
Please refer to Dave Wickert, I emailed him the test scenario. This is not
workable in a production environment.
Your point 2: Having to re-submit the credentials means that you've
submitted the credentials to IIS ( = basic auth), and then you need to submit
them again, as if you hadn't authentified. The point of authentifying is to
give you access to the application, having to authentify several times during
a session is not acceptable.
Have you been able to authentify to an AS2005 server through HTTP from
Excel? Please make sure you're not in the same domain as the server (Internet
scenario for instance), and verify that the userID making the connection to
AS2005 is the same userID specified in the HTTP connection string, and not
the windows user logged in at the client. (You need to use the profiler on
AS2005 to check who connected)
If you're able to achieve this from Excel, please post the method you used.
Several MS PSS engineers have tried it before without success.
>Other than these two issues, is there something else that is blocking you?
To sum it up, users need to enter their credentials several times every time
they open an Excel sheet, and they lose access to their existing AS2000
applications, since modifying the registry on demand is not an option.
"Akshai Mirchandani [MS]" wrote:
> Just to clarify -- as far as I know there are currently only two issues
> related to Basic authentication and HTTP on AS 2005:
>
> 1. If you use MSOLAP.3 to connect to an AS 2000 server over HTTP, it will
> not pass the userid/password correctly. This can be easily worked around by
> specifying Provider=MSOLAP.2 on the connection string of your application.
>
> 2. If you are inside Internet Explorer and browse to a web page and specify
> Basic authentication credentials. Then the web page inside IE tries to
> connect to an AS server over HTTP in the same "realm" -- for AS 2000, this
> would work without demanding the basic authentication credentials again. But
> in AS 2005, you are required to re-submit the credentials a second time.
> There is a good technical explanation for this which I won't go into, but
> the AS 2000 way was a little unsafe and it's probably a good thing that this
> doesn't work today...
>
> Other than these two issues, is there something else that is blocking you?
> Because in general Basic authentication over HTTP should work against AS
> 2005 and I'm not aware of any further issues there...
>
> I'm also missing some context in the statement below -- could you please
> expand on it?
>
>
> Thanks,
> Akshai
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> Please do not send email directly to this alias. This alias is for newsgroup
> purposes only.
>
> "John Lee" <John Lee@discussions.microsoft.com> wrote in message
> news:41F30399-D2CC-4B8A-BDF9- DC4587AE67A8@microso
ft.com...
>
>
>
| |
| John Lee 2005-12-15, 3:24 am |
| My concern looks like different issue from Pat's one. My symptom is simply
that username function for dynamic security does not work if connection user
in basic auth mode.
I guess it might be related to OLE DB 9 issue or Basic auth mechanism or
pump.dll. Anyway, I want to know whether it is a bug and then would be fixed.
Or I have to give up dynamic security in AS 2005.
Regards,
From John Lee
"Akshai Mirchandani [MS]" wrote:
> Just to clarify -- as far as I know there are currently only two issues
> related to Basic authentication and HTTP on AS 2005:
>
> 1. If you use MSOLAP.3 to connect to an AS 2000 server over HTTP, it will
> not pass the userid/password correctly. This can be easily worked around by
> specifying Provider=MSOLAP.2 on the connection string of your application.
>
> 2. If you are inside Internet Explorer and browse to a web page and specify
> Basic authentication credentials. Then the web page inside IE tries to
> connect to an AS server over HTTP in the same "realm" -- for AS 2000, this
> would work without demanding the basic authentication credentials again. But
> in AS 2005, you are required to re-submit the credentials a second time.
> There is a good technical explanation for this which I won't go into, but
> the AS 2000 way was a little unsafe and it's probably a good thing that this
> doesn't work today...
>
> Other than these two issues, is there something else that is blocking you?
> Because in general Basic authentication over HTTP should work against AS
> 2005 and I'm not aware of any further issues there...
>
> I'm also missing some context in the statement below -- could you please
> expand on it?
>
>
> Thanks,
> Akshai
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> Please do not send email directly to this alias. This alias is for newsgroup
> purposes only.
>
> "John Lee" <John Lee@discussions.microsoft.com> wrote in message
> news:41F30399-D2CC-4B8A-BDF9- DC4587AE67A8@microso
ft.com...
>
>
>
| |
| John Lee 2005-12-20, 3:24 am |
| Akshai, have you reproduce the symptom of username function issue? It
is very important for me to know if it is true or not. Thank you for
your effort in advance.
|
|
|
|
|