Problem w/ SQL Connection

Feb 27, 2008 at 9:42 PM
I'm using the SmartPart to embed a 3rd party grid control into a SharePoint page. Everything went fairly smooth considering this is the first time I've attempt it. The SmartPart is great! However, the grid isn't loading data.

The SQL Server is on one machine and the SharePoint site is on another. After creating the user control, I embedded it in a regular ASP.NET page and it worked. And the server setup was the same. I've tried tweaking the connection string (Trusted_Connection, Integrated Security) and just get a different error message in the trace file. From the error, it appears that the db user info isn't being parsed correctly. Any guidance would be greatly appreciated.

Here's the connection string...

Dim conn As New SqlConnection("Server=WACBLSQL03;Database=WACommissions;User ID=WACommWebUser;Password=$com!aww.;Trusted_Connection=True;")

Here's the error from the SharePoint trace.axd...

System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Goals.GetGoals(Object sender, EventArgs e)
Feb 28, 2008 at 7:13 PM
I'm still trying to fix this issue and decided to remove Trusted Connection from the connection string. Now it generates a different error that appears to be related to the grid DLL. Not sure if this is closer to resolving the problem or further away.

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.SpecialDemand(PermissionType whatPermission, StackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.DemandInternal(PermissionType permissionType)
at System.Runtime.Serialization.Formatters.Binary.ObjectWriter.Serialize(Object graph, Header[] inHeaders, __BinaryWriter serWriter, Boolean fCheck)
at System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Serialize(Stream serializationStream, Object graph, Header[] headers, Boolean fCheck)
at Obout.Grid.Grid.CompressDataSet(DataSet ds)
at Obout.Grid.Grid.set_DataSource(Object value)
at Goals.GetGoals(Object sender, EventArgs e)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Security.Permissions.SecurityPermission
The first permission that failed was:
<IPermission class="System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Flags="SerializationFormatter"/>

The demand was for:
<IPermission class="System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Flags="SerializationFormatter"/>

The granted set of the failing assembly was:
<PermissionSet class="System.Security.PermissionSet"
version="1">
<IPermission class="System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Flags="Execution"/>
<IPermission class="System.Security.Permissions.StrongNameIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
PublicKeyBlob="00240000048000009400000006020000002400005253413100040000010001000FB8197053C40B57272D15F0BEEA2B9B088A21609C78BE05CF1477EFE5FE8A5CD0CF6F45AE0C7A08E8BBACD59AD2A1D51D3F11D9B677FEF47A0918C5E9570B492A4D886314F58527DB3DC4AD4CCE1AAAF86115A3228CCF1CC39355A329F375CBCE41E214ACDD9C36752EC54A607EF8565B30AD09600FE1993B7660C8AE90A3C0"
Name="oboutGridNET"
AssemblyVersion="2.0.0.0"/>
<IPermission class="System.Security.Permissions.UrlIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Url="file:///C:/Inetpub/wwwroot/wss/VirtualDirectories/devwacom/bin/oboutGridNET.DLL"/>
<IPermission class="System.Security.Permissions.ZoneIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Zone="MyComputer"/>
<IPermission class="System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Level="Minimal"/>
<IPermission class="Microsoft.SharePoint.Security.WebPartPermission, Microsoft.SharePoint.Security, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"
version="1"
Connections="True"/>
</PermissionSet>

The assembly or AppDomain that failed was:
oboutGridNET, Version=2.0.0.0, Culture=neutral, PublicKeyToken=5ddc49d3b53e3f98
The method that caused the failure was:
Byte[] CompressDataSet(System.Data.DataSet)
The Zone of the assembly that failed was:
MyComputer
The Url of the assembly that failed was:
file:///C:/Inetpub/wwwroot/wss/VirtualDirectories/devwacom/bin/oboutGridNET.DLL



ccshine wrote:
I'm using the SmartPart to embed a 3rd party grid control into a SharePoint page. Everything went fairly smooth considering this is the first time I've attempt it. The SmartPart is great! However, the grid isn't loading data.

The SQL Server is on one machine and the SharePoint site is on another. After creating the user control, I embedded it in a regular ASP.NET page and it worked. And the server setup was the same. I've tried tweaking the connection string (Trusted_Connection, Integrated Security) and just get a different error message in the trace file. From the error, it appears that the db user info isn't being parsed correctly. Any guidance would be greatly appreciated.

Here's the connection string...

Dim conn As New SqlConnection("Server=WACBLSQL03;Database=WACommissions;User ID=WACommWebUser;Password=$com!aww.;Trusted_Connection=True;")

Here's the error from the SharePoint trace.axd...

System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Goals.GetGoals(Object sender, EventArgs e)

Coordinator
Feb 29, 2008 at 7:14 AM
Probably the 3rd party grid needs some special CAS permissions.

You can try to set the <trust level="Full" ... in the web.config (ONLY ON YOUR DEV BOX!!) to see if this solves the problem. A better solution would be to deploy the DLL's to the GAC instead of the BIN folder (including the grid's DLL's). But the best option would be to build a custom trust file for the needed permissions.
Mar 4, 2008 at 4:25 PM
Hi ccshine,

This sounds like the well-documented ‘Double Hop Issue’

Basically your IIS Server passes credentials to SharePoint but then as your SQL Server is on another PC when SharePoint tries to pass to SQLSVR it gets lost. This is known as the ‘Double Hop Issue’ and wouldn’t happen if they were all on the same PC. I had this with the same setup and same error. Here is how I got it cured:

You can read more on the double Hop issue here:

http://blogs.msdn.com/knowledgecast/archive/2007/01/31/the-double-hop-problem.aspx

WorkAround:

You can use whats called Impersonation with the RevertToSelf method and the SPSecurity.ElevatePermissions (part of the SharePoint Object Model) to do what you want.

Basically you get a null for the login because SQL SERVER cant validate the credentials. So.. you need to tell your code to forget using the credentials and use impersonation. (I.e let this user login but while this code is running execute it against someone else’s permissions). We use the RevertToSelf Method to tell IIS to fall back to its default account, now this will be your identity of your application pool account in IIS. Don’t worry if that has lowly permissions, that’s good, it should, (or in most cases not have full permissions as that would leave a large security risk wide open).
But don’t forget to cancel the RevertToSelf on Exit.

E.G

**********************************************************
Declare Function RevertToSelf Lib "advapi32.dll" () As Integer
Dim connectedUser As WindowsIdentity


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

connectedUser = WindowsIdentity.GetCurrent()
RevertToSelf()

End Sub

Alternativly you can try using the SPSecurity.ElevatePermissions which is what I use (http://msdn2.microsoft.com/en-gb/library/bb466220.aspx):

E.G

SPSecurity.RunWithElevatedPrivileges(New SPSecurity.CodeToRunElevated(AddressOf SubNameHere))

Only thing is what this is it screws if you try and return anything with your sub (sessions vars as a workaround).


Hope this is of some use.

JamesM
Mar 4, 2008 at 6:07 PM
We're having the double hop issue with Reporting Services in integrated mode as well. The only information I found related to Kerberos, and unfortunately implementing it would be extremely difficult as we'd be forced to engage our corporate office. The red tape would be endless. I'll give your suggestion a try.

Thanks!
Cathy