AdomdConnection permission problem

I am developing a Windows Form application. On one of the forms I display the results of a query to an Analysis Services database. I create the MDX command text by building a string.

Everything works, but users who do not have permission to access the database encounter and access error. I don't want to give all of the users permission to access the database. I want the application to use the credentials in the connection string. However, when I execute the AdomdCommand, it seems to be using the credentials of the logged on user instead of the "User ID" in the connection string.

How can I get the command to use the credentials in the connection string?

Dim cmdText As String = ""

Dim BdSalesBacklog As Decimal = 0

Dim oAdomdConnection As New AdomdConnection("Data Source=server;Catalog=PortalAnalytics;User ID=user;password=password")

Dim oAdomdCommand As AdomdCommand = New AdomdCommand()

Dim oAdomdReader As AdomdDataReader

Dim period As String = ddlPeriod.Items(0)

oAdomdCommand.CommandType = CommandType.Text

cmdText = "" & _

"SELECT " & _

"{ BNBTime.[" & period & "] } ON COLUMNS , " & _

"{ Measures.[Total Backlog Snapshot] } ON ROWS " & _

"FROM BNB "

oAdomdCommand.CommandText = cmdText

Try

oAdomdConnection.Open()

oAdomdCommand.Connection = oAdomdConnection

oAdomdReader = oAdomdCommand.ExecuteReader()

Catch ex As Exception

MessageBox.Show(Err.Description)

End Try

Do While oAdomdReader.Read()

BdSalesBacklog = oAdomdReader.GetDecimal(1)

Loop

oAdomdReader.Close()

oAdomdConnection.Close()

I called a developer friend of mine and he explained to me that SSAS requires the logged on user to have permissions in a Role in the AS database.

I created an AD group containing the users to whom I want to allow access, and I gave that group membership in a new AS Role. I assigned the role the permissions I wanted the users to have.

My application now works for all the users who need to run it.

Thanks

AdomdConnection permission problem

I am developing a Windows Form application. On one of the forms I display the results of a query to an Analysis Services database. I create the MDX command text by building a string.

Everything works, but users who do not have permission to access the database encounter and access error. I don't want to give all of the users permission to access the database. I want the application to use the credentials in the connection string. However, when I execute the AdomdCommand, it seems to be using the credentials of the logged on user instead of the "User ID" in the connection string.

How can I get the command to use the credentials in the connection string?

Dim cmdText As String = ""

Dim BdSalesBacklog As Decimal = 0

Dim oAdomdConnection As New AdomdConnection("Data Source=server;Catalog=PortalAnalytics;User ID=user;password=password")

Dim oAdomdCommand As AdomdCommand = New AdomdCommand()

Dim oAdomdReader As AdomdDataReader

Dim period As String = ddlPeriod.Items(0)

oAdomdCommand.CommandType = CommandType.Text

cmdText = "" & _

"SELECT " & _

"{ BNBTime.[" & period & "] } ON COLUMNS , " & _

"{ Measures.[Total Backlog Snapshot] } ON ROWS " & _

"FROM BNB "

oAdomdCommand.CommandText = cmdText

Try

oAdomdConnection.Open()

oAdomdCommand.Connection = oAdomdConnection

oAdomdReader = oAdomdCommand.ExecuteReader()

Catch ex As Exception

MessageBox.Show(Err.Description)

End Try

Do While oAdomdReader.Read()

BdSalesBacklog = oAdomdReader.GetDecimal(1)

Loop

oAdomdReader.Close()

oAdomdConnection.Close()

I called a developer friend of mine and he explained to me that SSAS requires the logged on user to have permissions in a Role in the AS database.

I created an AD group containing the users to whom I want to allow access, and I gave that group membership in a new AS Role. I assigned the role the permissions I wanted the users to have.

My application now works for all the users who need to run it.

Thanks

ADO Connection to Backend SQL Server

I am using MS Access forms as a front end to a backend SQL Server
Database. I am trying to use an ADO connection to return a Select
Statement from the SQL Server to an Access form to be viewable by the
user. I have been able to establish the connection to the SQL Server
and have verified that the SQL statement is correct. I am completely
new to ADO and I can't figure out how to display the data returned in
the ADO recordset. Could someone please help me out with this? Is
there a way to display the returned recordset in an Access
form/datasheet to be viewable by the user? Below is my code for your
reference:
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
Dim Conn As Connection
Dim RS As Recordset
Dim LOC
Dim SQL
LOC = "PROVIDER=SQLOLEDB;DRIVER={SQL
Server};SERVER=MyServer;DATABASE=MyDBase;UID=MyID; PWD=MyPWD"
SQL = _
"SELECT * From tblClaim"
Set Conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
Conn.Open LOC
RS.Open SQL, Conn, adOpenKeyset
Me.RecordSource = RS
RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing
End Sub
Much simpler to make use of the linked tables. Make the form's source the
linked table or use an access query based on the linked table.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<ashleycvernon@.gmail.com> wrote:
>I am using MS Access forms as a front end to a backend SQL Server
> Database. I am trying to use an ADO connection to return a Select
> Statement from the SQL Server to an Access form to be viewable by the
> user. I have been able to establish the connection to the SQL Server
> and have verified that the SQL statement is correct. I am completely
> new to ADO and I can't figure out how to display the data returned in
> the ADO recordset. Could someone please help me out with this? Is
> there a way to display the returned recordset in an Access
> form/datasheet to be viewable by the user? Below is my code for your
> reference:
> Option Compare Database
> Option Explicit
> Private Sub Form_Open(Cancel As Integer)
> Dim Conn As Connection
> Dim RS As Recordset
> Dim LOC
> Dim SQL
> LOC = "PROVIDER=SQLOLEDB;DRIVER={SQL
> Server};SERVER=MyServer;DATABASE=MyDBase;UID=MyID; PWD=MyPWD"
> SQL = _
> "SELECT * From tblClaim"
> Set Conn = CreateObject("ADODB.Connection")
> Set RS = CreateObject("ADODB.Recordset")
> Conn.Open LOC
> RS.Open SQL, Conn, adOpenKeyset
> Me.RecordSource = RS
> RS.Close
> Set RS = Nothing
> Conn.Close
> Set Conn = Nothing
> End Sub
>
|||Thanks Dave,
I am currently utilizing the linked tables to pass information to and
from the SQL server, but wanted to move to ADO connections so I could
get rid of the tables in the Access application. One of the tables
contains password info, so I didn't want there to be any way for a user
to access this table. Maybe ADO isn't the way around this; any links
or tips on securing linked tables in Access would be much appreciated.
-Ashley
Dave Patrick wrote:[vbcol=seagreen]
> Much simpler to make use of the linked tables. Make the form's source the
> linked table or use an access query based on the linked table.
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> <ashleycvernon@.gmail.com> wrote:
|||Personally I'd do the security on SQL server rather than trying to do in
Access. I'd use windows authentication rather than sql logins. You could
also link to a view rather than the table and setup security on the view
based on windows user and or windows group association.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<ashleycvernon@.gmail.com> wrote:
> Thanks Dave,
> I am currently utilizing the linked tables to pass information to and
> from the SQL server, but wanted to move to ADO connections so I could
> get rid of the tables in the Access application. One of the tables
> contains password info, so I didn't want there to be any way for a user
> to access this table. Maybe ADO isn't the way around this; any links
> or tips on securing linked tables in Access would be much appreciated.
> -Ashley

ADO connection

In VB6 I use more Forms where users can Add, Edit, Delete Records from more tables.

WHICH IS THE BEST METHOD

1. To open a connection when user start the application and I close the connection when user leave the application.

In Login form (Public cn As ADODB.Connection)

SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Password='" & Pass & "'" & _
";Persist Security Info=False" & _
";User ID='" & UserName & "'" & _
";Initial Catalog='" & DataBaseName & "'" & _
";Data Source='" & ServerName & "'"

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

OR

2. To Open a connection in each Form and close the connection when Form is UnLoadetThe first case is not possible because

Set cn = New ADODB.Connection (this is in Form Login)
In other form it is necesary to set the connection again.|||The rule is that you only keep the connection open as long as needed - and no longer. What are your concerns ?|||How many clients will use this application ?|||5-8 users|||Will the security credentials be different for each user ? What is the purpose for the application and the multiple forms ? Are you thinking about opening 1 connection object that will be used for multiple recordset/command objects ?|||How I can open a connection when user start the application and kepp open until user leave the application ?

In module:
Public cn As ADODB.Connection

In first form when is load:
SirConectare_SQL = "Provider=SQLOLEDB.1" & _
";Password='" & Pass & "'" & _
";Persist Security Info=False" & _
";User ID='" & UserName & "'" & _
";Initial Catalog='" & DataBaseName & "'" & _
";Data Source='" & ServerName & "'"

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

If I want to use "cn" in other forms is not possible (is not open)|||Are you destroying/closing the connection before the 2nd form is loaded ?|||I don`t close the connection, but I close (Unload) the first form.
The connection is closed ?|||Where are you declaring - Public cn As ADODB.Connection ?|||This declaration is made on a Module

Public cn As ADODB.Connection

In the Startup Form (where user input UserName and Password)

Set cn = New ADODB.Connection

With cn
.ConnectionString = SirConectare_SQL
.Open
End With

If I want to use this connection on other forms I can't because is Closed.
I want to open the connection only one time(when application startup), and if it possible to use the connection in all form.|||Open the connection in a module or class when login in form successfull. Close it when application main form is closed.|||This Looks Like VB code If it is Do not forget to set the DBconnection ( and all all objects associated to it) = nothing

set cn = nothing

I personnally like to use 1 public DB connection instead of multiple connections per app.

Hope this helps.
LJ|||Hello Gurus out there!

I want to know what would be the best method in opening a connection to server:

1. a connection (declared globally) that is open once during Login and access thru all forms and be closed only when application is terminated
What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

2. a connection is opened only when needed but everytime i execute a query against the database i have to open also that connection and terminate when it is not used...What is the advantage/disadvantage of this method in my SQL Server 2k resources or in any RDBMS?

Secondly, how can we know the resources used by the users that are connected to my SQL SERVER in terms of memory usage and CPU?

Im using VB/FOxPro and I want to know the best practice in terms of opening a connection to the database coz Im expecting to have 20 or more users online simultanously and hook to my server as soon as we are finished with our system.

I hope you can light up our minds with these concerns.

Thanks,

Bernie|||Bernie - What driver are you using ?