It seems a bug of SQL Server 2005.

I do the following steps:

1:Use Manage Studio login the server with Integrated security.

2:Create a dabase named testdb;

3:Create a SQL Server login named amber ,and set it to be dbowner of testdb;

4:Create a SQL Server login named guxiaobo ,set it's default databse to be testbd,and in the testdb databse map login guxiaobo to user guxiaobo;

5:Close Manage Studio and reopen it ,this time use login amber log to the server.

6:In database testbd create a databse role role1 owned by dbo;

7:In database testdb create a schema schema1 owned by dbo;

8:Set user guxiaobo to be member of role1,and set guxiaobo's default schema to be schema1;

9:In the schema properties-schema1 dialog choose permissions,in the users or roles listview I add role1 to it.in the explicit permissions for role1 listbox,I choose select /update/insert/delete/view definition grant checkboxes.and apply the selects.

10:In the schema1 schema I create a table:create table a(a int ,b varchar(10));

11:In the dabase role properties-role1 dialog choose securables panel ,int securable listview I add the schema1.a table,and in explicit permissions for schema1.a I choose all for grant ,then apply the selects.

12:Now I use login guxiaobo to log into dabase and issue "select * from a",but got a error msg saying guxiaobo has not enough permission to select form table a.

Does anyone has found anything I missed?

And in step 11 ,I choose all the columns of table a for all permissions.|||Anybody helps ?

Issues setting up users to authenticate via NT group into MSSQL

I have a NT Group of users that need to connect to MSSQL db using integrated auth and run a stored proc. I have got this concept to work for individual windows users but not a group.

Does anyone have any ideas?

(Win XP, MSSQL 2000 SP3, Windows Group = "SQLUsers", member is "User2")

-- TSQL --

USE master
GO
sp_grantlogin 'pc\SQLUsers'
GO
sp_defaultdb 'pc\SQLUsers', 'theDB'
GO
USE theDB
GO
sp_grantdbaccess 'pc\SQLUsers', 'SQLUsers'
GO
GRANT EXECUTE ON [dbo].[uspThing] TO SQLUsers
GO

-- Trying to connect with OSQL.EXE:

> osql -d theDB -E
Login failed for user 'pc\User2'.

BUT....

runing it for the individual user:

--TSQL

USE master
GO
sp_grantlogin 'pc\User2'
GO
sp_defaultdb 'pc\User2', 'theDB'
GO
USE theDB
GO
sp_grantdbaccess 'pc\User2', 'SQLUsers'
GO
GRANT EXECUTE ON [dbo].[uspThing] TO SQLUsers
GO

-- now trying to connect with OSQL.EXE as User2:

>osql -d theDB -E
1> exec uspThing
2> go
3> data...

all is fine.....

I can't use the user method - to much admin... any ideas!?

PKOk, get this... because I was using "runas /user:User2 cmd.exe" sessions for testing the different users integrated security (osql -E -d dbName) etc the group changes I was making were not reflected until I logged out of the command session.

I have been officially driven mad by this one...

Issue doing Integrated services programming using Microsoft.SqlServer.ManagedDTS.dll

Hi,

I am writing an installer code in C# to deploy the SSIS package.

I want to use Microsoft.SqlServer.ManagedDTS.dll for it.

It is mentioned in few articles available online that Microsoft.SqlServer.ManagedDTS.dll ships with SQL Server 2005.

I searched on our database server but could not get it.

Anyone having idea on this please help.

HV

Don't try and install SSIS by hand, it is not a good idea It is not supposed to be a redistributable component. There are a lot more assemblies that just that one for SSIS.

Since SSIS requires a full SQL Server license, why not use the regular SQL setup to do this for you. You can choose which servers you want.

If you have installed SSIS on your server then it will certainly be in the GAC, but it may not be on the file system outside of this, unless you installed the tools, in which case it is - C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

|||Another question to ask: Is SSIS installed on this SQL Server box? A lot of SQL 2005 servers will be set up by their DBAs to not have any unnecessary components installed, and SSIS sometimes falls into this category.|||

Hi,

Thank you Darren.

SSIS is installed on the machine.

I checked the following folder :- C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

but dll is not available.

Which tool installation delivers Microsoft.SqlServer.ManagedDts.dll?

Please let me know.

HV

|||

Hi,

Thank you Mathew.

SSIS is installed on the machine.

I checked the following folder :- C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

but it's not available.

HV

|||

Hi,

I picked the Microsoft.SQLServer.ManagedDTS.dll from following folder:

C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91>

Similarly picked Microsoft.SqlServer.DTSRuntimeWrap.dll also.

I added it as reference in my .NET application.

When I execute the program I get below error:

Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.

Any Clue?

How to get the RunningPackages information back to a client PC?

HV

|||

If you want running packages information, why not just use the MS tools?

Do you have SSIS tools installed on the local machine that hosts the program? The error indicates that Microsoft.SqlServer.DTSRuntimeWrap is not installed on the local PC?

|||

No SSIS is not installed on the machine which hosts program.

But that is my requirement , I want to deploy SSIS package remotely.

I have installed Microsoft.SQLServer.DTSRuntimeWrap.dll in GAC.

Let me know if there is a way out to use it on a machine where SSIS is not installed.

HV

|||

SSIS is not installed you say, and you get an error that says in cannot find a COM component. Do you think there may be a connection?

I refer you to my original post, apart from saying that a manual install was a silly idea, I also pointed out "There are a lot more assemblies that just that one for SSIS."

As a start point that DLL is just a wrapper to the COM library that does the work, the name hints at that, and the error proves that it is trying to use a COM DLL that is not there, a COM DLL with a ProgID of {E44847F1-FD8C-4251-B5DA-B04BB22E236E} perhaps. As I said before there are lots of DLLs involved in SSIS not just one, so use a proper install.

What you are asking for a not a supported scenario, you may be violating your license agreements if not careful, and at any rate will be a very complicated task to try and reverse engineer the requirements, and very slow if you cannot track a missing COM DLL down yourself.

If you think this is wrong, post feedback to MS (http://connect.microsoft.com) telling them why you think you should have some redistribuatable support, but in the mean-time you will need to run one of the MS installs to get this to work.

Why do you not want to use a MS install?

Issue connecting from C#, even though SSPI, it does ANONYMOUS LOGON

We are using Integrated Security=SSPI, and I can connect to SSAS 2005 just fine. I can also connect to the cube through OWC...

but when I try to connect in C# (.net 1.1), I get the message: the peer prematurely closed the connection. I did plenty of searches on this message, and I should have all the required files to connect - it just doesn't connect.

So I did a trace, and when I connect through OWC, it shows an Audit Log with my username. When I try to connect w/ .net, it shows ANONYMOUS LOGON

Here is my connection string that is being used: (xxx's are filled w/ the right values)
Provider=MSOLAP.3;Cache Authentication=True;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xxx;Data Source=xxxx;

Any ideas?

I realized that this might be happening because the virtual directory was set to use anonymous access. So, I took that off of the directory and made sure 'integrated windows authentication' was checked. I did an iisreset and tried out the changes, but it still was using ANONYMOUS LOGON.

I then tried to make a normal windows app because before, I was using a web app. This connected with my name just fine!

So it seems like the problem is with the iis virtual directory setup still wanting to use the anonymous account. I even set deny permission to the web project directory for iusr, iwam, network service.

I'd be glad to receive some feedback.

|||Its the double hop scenario. The user comes in with credentials and they get used to access the web site/service. But when the web site/service goes to the db, the ASP.Net (local) user is used for authentication.

That is why it works when one runs it outside the web site/service but not within. If it is IIS 6 you can try the application pooling in InetMgr by specifying a user that has access and the have the site use that pool...your mileage may vary.

Otherwise you will need to connect not using the impersonation route but by passing in a DB user name and password.

Another option is Active Directory and having the machine accepted while using Kerberos Authentication...but again, your mileage may vary.|||

There is a need to implement the specific user's crudentials when accessing SSAS. It sounds like one option you gave would still be using a single logon account.

If we go the route of active directory, is there a way to pass the current user's logon info but not have it be visible to developers? the connection open method takes a (plain text?) un/pw if SSPI isn't used.

|||You may want to look into Protected Configuration as provided by ASP.Net 2. That would encrypt the information into the web.config. See this Security Practices: ASP.NET 2.0 Security Practices at a Glance to see if that answers your question, search for (encryption). Otherwise one of the other notes may be beneficial.|||Were you able to get a resolution to this issue? I have a .NET application that I am trying to pass role-based security into SSAS 2005. This is a double-hop scenario and we have implemented Active Directory to try to restrict access to dimension data. However, it is not passing through the appropriate security restriction to the dimension data.

Issue connecting from C#, even though SSPI, it does ANONYMOUS LOGON

We are using Integrated Security=SSPI, and I can connect to SSAS 2005 just fine. I can also connect to the cube through OWC...

but when I try to connect in C# (.net 1.1), I get the message: the peer prematurely closed the connection. I did plenty of searches on this message, and I should have all the required files to connect - it just doesn't connect.

So I did a trace, and when I connect through OWC, it shows an Audit Log with my username. When I try to connect w/ .net, it shows ANONYMOUS LOGON

Here is my connection string that is being used: (xxx's are filled w/ the right values)
Provider=MSOLAP.3;Cache Authentication=True;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xxx;Data Source=xxxx;

Any ideas?

I realized that this might be happening because the virtual directory was set to use anonymous access. So, I took that off of the directory and made sure 'integrated windows authentication' was checked. I did an iisreset and tried out the changes, but it still was using ANONYMOUS LOGON.

I then tried to make a normal windows app because before, I was using a web app. This connected with my name just fine!

So it seems like the problem is with the iis virtual directory setup still wanting to use the anonymous account. I even set deny permission to the web project directory for iusr, iwam, network service.

I'd be glad to receive some feedback.

|||Its the double hop scenario. The user comes in with credentials and they get used to access the web site/service. But when the web site/service goes to the db, the ASP.Net (local) user is used for authentication.

That is why it works when one runs it outside the web site/service but not within. If it is IIS 6 you can try the application pooling in InetMgr by specifying a user that has access and the have the site use that pool...your mileage may vary.

Otherwise you will need to connect not using the impersonation route but by passing in a DB user name and password.

Another option is Active Directory and having the machine accepted while using Kerberos Authentication...but again, your mileage may vary.|||

There is a need to implement the specific user's crudentials when accessing SSAS. It sounds like one option you gave would still be using a single logon account.

If we go the route of active directory, is there a way to pass the current user's logon info but not have it be visible to developers? the connection open method takes a (plain text?) un/pw if SSPI isn't used.

|||You may want to look into Protected Configuration as provided by ASP.Net 2. That would encrypt the information into the web.config. See this Security Practices: ASP.NET 2.0 Security Practices at a Glance to see if that answers your question, search for (encryption). Otherwise one of the other notes may be beneficial.|||Were you able to get a resolution to this issue? I have a .NET application that I am trying to pass role-based security into SSAS 2005. This is a double-hop scenario and we have implemented Active Directory to try to restrict access to dimension data. However, it is not passing through the appropriate security restriction to the dimension data.