Active Directory Groups Don't Work with Linked Server?

I mapped a login created with an Active Directory Group on server A to a login on server B through a linked server on server A and received a null login error when attempting to connect.

I changed the Active Directory Group login to an individual active directory login and the connection worked fine.

I saw someone post online somewhere that Active Directory Groups don't work with linked server by design--but I wanted to get confirmation on this. Can anyone confirm this, particularly someone from Microsoft?

This might be the dreaded "Double-hop" scenario. There's a great rundown by a Microsoft Protocols Engineer here:

http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

That will get you started on learning why this is happening.

Active Directory Groups Don't Work with Linked Server?

I mapped a login created with an Active Directory Group on server A to a login on server B through a linked server on server A and received a null login error when attempting to connect.

I changed the Active Directory Group login to an individual active directory login and the connection worked fine.

I saw someone post online somewhere that Active Directory Groups don't work with linked server by design--but I wanted to get confirmation on this. Can anyone confirm this, particularly someone from Microsoft?

This might be the dreaded "Double-hop" scenario. There's a great rundown by a Microsoft Protocols Engineer here:

http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

That will get you started on learning why this is happening.

AcquireConnection returns null value

I have the following code in my custom source component's AcquireConnection method -

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;

if (cmado == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.");

// Get the underlying connection object.
this.oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;

if (oledbConnection == null)

throw new Exception("The ConnectionManager is not an ADO.NET connection.");

isConnected = true;

}

The value of oledbConnection is null.

I am trying to invoke the above method within a package that I am trying to create dynamically. Any help ?

private const string ADONETConnectionString = "Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

ConnectionManager PubTRCM = package.Connections.Add("ADO.NET:OLEDB");

PubTRCM.Name = "PubTR";

PubTRCM.ConnectionString = String.Format(ADONETConnectionString, paramPubServer, paramPubTRDB);

#region Create OLEDB Source Component

//Create a OLEDB Source Component
IDTSComponentMetaData90 Source = dataFlow.ComponentMetaDataCollection.New();
Source.Name = "OLEDBSource";

Source.ComponentClassID = typeof(Microsoft.Samples.SqlServer.Dts.AdoSourceSample).AssemblyQualifiedName;

//Get the design time instance of the source
CManagedComponentWrapper SourceDesignTime = Source.Instantiate();

//Initialize the component
SourceDesignTime.ProvideComponentProperties();

if (Source.RuntimeConnectionCollection.Count > 0)
{
Source.RuntimeConnectionCollection[0].ConnectionManagerID = PubTRCM.ID;
Source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(PubTRCM);
}

SourceDesignTime.SetComponentProperty("SqlStatement", "Select PlantId from PlantDim");

//Reinitialize the metadata
SourceDesignTime.AcquireConnections(null);
SourceDesignTime.ReinitializeMetaData();
SourceDesignTime.ReleaseConnections();

#endregion

So what stage does the problem start? Is cmado an object? If so what type? What is the result from cmado.AcquireConnection, never mind the cast?

Have you tried debugging your component whilst it is being called like this?

Does the component work in the designer, I assume not.

|||

I could resolve this problem by changing the connection string from -

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

to

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;";

and adding the connection manager to the package as follows -

ConnectionManager CMgr = package.Connections.Add("ADO.NETystem.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089");

Thanks,

Reni

AcquireConnection returns null value

I have the following code in my custom source component's AcquireConnection method -

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;

if (cmado == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.");

// Get the underlying connection object.
this.oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;

if (oledbConnection == null)

throw new Exception("The ConnectionManager is not an ADO.NET connection.");

isConnected = true;

}

The value of oledbConnection is null.

I am trying to invoke the above method within a package that I am trying to create dynamically. Any help ?

private const string ADONETConnectionString = "Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

ConnectionManager PubTRCM = package.Connections.Add("ADO.NET:OLEDB");

PubTRCM.Name = "PubTR";

PubTRCM.ConnectionString = String.Format(ADONETConnectionString, paramPubServer, paramPubTRDB);

#region Create OLEDB Source Component

//Create a OLEDB Source Component
IDTSComponentMetaData90 Source = dataFlow.ComponentMetaDataCollection.New();
Source.Name = "OLEDBSource";

Source.ComponentClassID = typeof(Microsoft.Samples.SqlServer.Dts.AdoSourceSample).AssemblyQualifiedName;

//Get the design time instance of the source
CManagedComponentWrapper SourceDesignTime = Source.Instantiate();

//Initialize the component
SourceDesignTime.ProvideComponentProperties();

if (Source.RuntimeConnectionCollection.Count > 0)
{
Source.RuntimeConnectionCollection[0].ConnectionManagerID = PubTRCM.ID;
Source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(PubTRCM);
}

SourceDesignTime.SetComponentProperty("SqlStatement", "Select PlantId from PlantDim");

//Reinitialize the metadata
SourceDesignTime.AcquireConnections(null);
SourceDesignTime.ReinitializeMetaData();
SourceDesignTime.ReleaseConnections();

#endregion

So what stage does the problem start? Is cmado an object? If so what type? What is the result from cmado.AcquireConnection, never mind the cast?

Have you tried debugging your component whilst it is being called like this?

Does the component work in the designer, I assume not.

|||

I could resolve this problem by changing the connection string from -

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

to

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;";

and adding the connection manager to the package as follows -

ConnectionManager CMgr = package.Connections.Add("ADO.NETystem.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089");

Thanks,

Reni

AcquireConnection returns null

Hi,

we are facing some issue to get the underlying OledbConnection from the runtime ConnectionManager.

below is the code sample that we are using

IDtsConnectionService conService = (IDtsConnectionService)this.serviceProvider.GetService(typeof(IDtsConnectionService));

if (conService == null)

return;

ArrayList conCollection = conService.GetConnectionsOfType("OLEDB");

for (int count = 0; count < conCollection.Count; count++)

{

string conName = ((ConnectionManager)conCollection[count]).Name;

if (conName == conMgrname)

{

conMgr = DtsConvert.ToConnectionManager90((ConnectionManager)conCollection[count]);

ConnectionManager cm = DtsConvert.ToConnectionManager(conMgr);

Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet cmado = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet;

OleDbConnection conn= cmado.AcquireConnection(null) as OleDbConnection;

}

}

In the sample above the conn is null.

To this query Darren replied to use the connection of type ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 to create the connection.

Your call to GetConnectionsOfType("OLEDB"); will return native OLE-DB connections, not ADO.NET connections, using the OleDbConnection.

The connection type for the ADo>NET OLE-DB connection is -

ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

but if user creates an New OLEDB Connection from the Connection Manager panel of the BIDS and selects the same from the custom UI how to get the underlying OLEDBConnection? the CreationName in this case is "OLEDB" and not ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

Replace the code inside the if branch with this:

ConnectionManager cm = conCollection[count] as ConnectionManager;

OleDbConnection conn= cm.AcquireConnection(null) as OleDbConnection;

HTH.

|||

Hi bob,

It still returns me null.

Thanks,

Dharmbir

|||

You are right. OLE DB connection manager uses native connections internally and the AcquireConnection method does not know how to cast it to managed OleDbConnection.

You may try if the following will give you the managed connection:

oleDbConnection = ((IDTSConnectionManagerDatabaseParameters90)connectionManager.InnerObject).GetConnectionForSchema() as OleDbConnection;

However, I am not confident you can use it in the execution time.

HTH.

|||

Hi Bob,

thanks a lot. it really worked fine.

now i have one more issue regarding the connection informaction. I am using OracleConnection at runtime and for this reason i want to fetch the user id, password and datasource information from the connectionstring of OledbConnection. but the connection string doesn't exposes the password.

Can you please help me out for this?

Thanks,

Dharmbir

|||There is no way to get a password from a connection manager because it is a sensitive information. I would go with limiting your component to only use ADO.NET OLE DB connections (the one you can get using AcquireConnection) or provide a way for the users to enter missing passwords.|||

Hi All!

I have a problem with AcquireConnection in c#... I wrote this code:

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmAdo = cm.InnerObject as ConnectionManagerAdoNet;

if (cmAdo == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO connection.");

this.conn = cmAdo.AcquireConnection(transaction) as OracleConnection;

}

but the 'conn' is ALWAYS null...

I try

this.conn = ((IDTSConnectionManagerDatabaseParameters90)cmAdo).GetConnectionForSchema() as OracleConnection;

too, but no result: the 'conn' is null again...

Nobody help me?

Martina

AcquireConnection returns null

Hi,

we are facing some issue to get the underlying OledbConnection from the runtime ConnectionManager.

below is the code sample that we are using

IDtsConnectionService conService = (IDtsConnectionService)this.serviceProvider.GetService(typeof(IDtsConnectionService));

if (conService == null)

return;

ArrayList conCollection = conService.GetConnectionsOfType("OLEDB");

for (int count = 0; count < conCollection.Count; count++)

{

string conName = ((ConnectionManager)conCollection[count]).Name;

if (conName == conMgrname)

{

conMgr = DtsConvert.ToConnectionManager90((ConnectionManager)conCollection[count]);

ConnectionManager cm = DtsConvert.ToConnectionManager(conMgr);

Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet cmado = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet;

OleDbConnection conn= cmado.AcquireConnection(null) as OleDbConnection;

}

}

In the sample above the conn is null.

To this query Darren replied to use the connection of type ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 to create the connection.

Your call to GetConnectionsOfType("OLEDB"); will return native OLE-DB connections, not ADO.NET connections, using the OleDbConnection.

The connection type for the ADo>NET OLE-DB connection is -

ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

but if user creates an New OLEDB Connection from the Connection Manager panel of the BIDS and selects the same from the custom UI how to get the underlying OLEDBConnection? the CreationName in this case is "OLEDB" and not ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

Replace the code inside the if branch with this:

ConnectionManager cm = conCollection[count] as ConnectionManager;

OleDbConnection conn= cm.AcquireConnection(null) as OleDbConnection;

HTH.

|||

Hi bob,

It still returns me null.

Thanks,

Dharmbir

|||

You are right. OLE DB connection manager uses native connections internally and the AcquireConnection method does not know how to cast it to managed OleDbConnection.

You may try if the following will give you the managed connection:

oleDbConnection = ((IDTSConnectionManagerDatabaseParameters90)connectionManager.InnerObject).GetConnectionForSchema() as OleDbConnection;

However, I am not confident you can use it in the execution time.

HTH.

|||

Hi Bob,

thanks a lot. it really worked fine.

now i have one more issue regarding the connection informaction. I am using OracleConnection at runtime and for this reason i want to fetch the user id, password and datasource information from the connectionstring of OledbConnection. but the connection string doesn't exposes the password.

Can you please help me out for this?

Thanks,

Dharmbir

|||There is no way to get a password from a connection manager because it is a sensitive information. I would go with limiting your component to only use ADO.NET OLE DB connections (the one you can get using AcquireConnection) or provide a way for the users to enter missing passwords.|||

Hi All!

I have a problem with AcquireConnection in c#... I wrote this code:

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmAdo = cm.InnerObject as ConnectionManagerAdoNet;

if (cmAdo == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO connection.");

this.conn = cmAdo.AcquireConnection(transaction) as OracleConnection;

}

but the 'conn' is ALWAYS null...

I try

this.conn = ((IDTSConnectionManagerDatabaseParameters90)cmAdo).GetConnectionForSchema() as OracleConnection;

too, but no result: the 'conn' is null again...

Nobody help me?

Martina

Accessing Null UDT's from TSQL

I created a UDT in C# that is working fine except when I request rows where the type is null.

If I use SELECT Film.ToString() FROM ImageData it works fine, or any property they return Null as expected

but if I use SELECT Film FROM ImageData it produces the error below

An error occurred while executing batch. Error message is: Data is Null. This method or property cannot be called on Null values.

I looked through all the examples in C# and my code matches the pattern for nulls exactly. Can anyone shead some light on what attribute I may have to add or method I should override which is not documented.

Could you please check the following topics in BOL?

http://msdn2.microsoft.com/es-es/library/ms131069.aspx

http://msdn2.microsoft.com/en-us/library/a8s4s5dz.aspx

When you query the column directly you should get either NULL for the NULL values or the binary representation of the UDT.

Can you check your code against the examples shown in the links above? There is a separate forum for SQL Server CLR that you can follow up also.

|||

This was a bugPlease see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125552

Cheers,