Dan Balaceanu

Hi,

I have a SingleServer instalation for MOSS.

I get this "Unable to connect..." error in BDC Item list web part. I think is something with the credentials the goes to SQL Server.

<LobSystemInstance Name="Charisma TS">
<Properties>
<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
<Property Name="AuthenticationMode" Type="System.String">RevertToSelf</Property>
<Property Name="RdbConnection Data Source" Type="System.String">MyServer</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">DBName</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
<Property Name="RdbConnection Pooling" Type="System.String">false</Property>
</Properties>
</LobSystemInstance>

Any ideas

In SQl PRofiler i see this:"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

I thinks becouse is a single server deployment i don't have to configure SSO, right

Thanks,

Dan



Re: SharePoint - Business Data Catalog Unable to connect ...

Nick Swan

hey,

if you set the authentication mode to PassThrough, it will try to retrieve the data from SQL Server as the user who is viewing the page in MOSS. If that user has the appropriate rights to access SQL Server and retrieve data it should be returned fine

Hope that helps

Nick






Re: SharePoint - Business Data Catalog Unable to connect ...

SharePointing

If you use passthrough it can really kill performance since the connection pool on the SQL Server is based on user/connections. If you use RevertToSelf is that making the call as the Application Pool Identity If so, does that user have rights to the database




Re: SharePoint - Business Data Catalog Unable to connect ...

Dan Balaceanu

Hi Nick,

Thanks for the reply. It is working now. It was my mistake. The Data source was actually a second server, so, I think is a second loop for security, and the credentials are lost between web server and backend database server.

Is this scenario, right

¡°When the Business Data Catalog is accessed from a Web page, it runs in the Microsoft Internet Information Services (IIS) worker process, w3wp.exe. The identity of this process is the IIS application pool account impersonating the logged-on user. To avoid losing the logged-on user¡¯s identity when the Business Data Catalog authenticates to the back-end server, you must enable Kerberos delegation between the server running IIS and the other computer. Kerberos delegation enables a receiving server to send the authentication request to the proper location.¡± http://msdn2.microsoft.com/en-us/library/ms566523.aspx

After I switched to a database on the local computer (where MOSS is) it works.

Because I want to authenticate to backend database server using SQL credential, I have to use SSO, right Or use Kerberos in order to have delegation... I will go with SSO.

Thanks Nick,

Dan





Re: SharePoint - Business Data Catalog Unable to connect ...

Nick Swan

Hi Dan,

I'll be honest almost all the testing and work I've done with BDC while building BDC Meta Man has been on a single server, but from what you've pointed out in the SDK it seems you will need to get SSO working (or kerberos). I thought pass through worked even across servers out of the box but obviously not. Is the account your application pool is running under on MOSS a domain account or local one

I'll try and get this scenario setup over the next week, lets keep each other updated as to how it goes.

Cheers

Nick






Re: SharePoint - Business Data Catalog Unable to connect ...

hwg_Maarten

I worked around this by changing passthrough to reverttoself and giving the sharepoint server access to the sql database using domain\machine$. I then used sharepoint permissions to define access to the data and read/write permissions.

good luck




Re: SharePoint - Business Data Catalog Unable to connect ...

ieka

hai..

i just want to ask u a few question. im using 2 servers rite now. one is MOSS and another one is SQL server 2005. this sql is running on virtual server. Im using the AdventureWorksDW database sample and i can import the application definition in my MOSS. but when i try to create a business data list web part...an error occured. "Unable to connect to AdventureWorksDW". i have no idea why.

can u tell me what am i suppose to do

thanks a lot





Re: SharePoint - Business Data Catalog Unable to connect ...

JavierD - MSFT

ikea,

Did the above solved your issue





Re: SharePoint - Business Data Catalog Unable to connect ...

Fred33

Have that problem, MOSS on a server, SQL on another, tried PassTrought and RevertToSelf but got the same error, Unable to Connect TO "BD name"



Re: SharePoint - Business Data Catalog Unable to connect ...

JavierD - MSFT

The problem si that the identity of the caller can't be passes across machines. This is not related to MOSS or BDC.

The reason is that the security design of windows does not allow to pass identities across machines. You can fins more about the "double hop" issue here: http://blogs.msdn.com/knowledgecast/archive/2007/01/31/the-double-hop-problem.aspx

SSO can be an alternative, implementation will depend on your requieriments in terms of security, infrastucture, etc. Also using revert to self and make calls to the db as a trusted subsystem may work but you need to make sure is secure for the aplpication you are working. (See security best practices on MSDN site).

Javier





Re: SharePoint - Business Data Catalog Unable to connect ...

bobchauvin

Would KERBEROS help here




Re: SharePoint - Business Data Catalog Unable to connect ...

Fred33

What I did is finally in the xml file add the username and password of the admin so they can connect to the BD and removed the SSPI, work but I dont think its a good practice to write those in the xml file



Re: SharePoint - Business Data Catalog Unable to connect ...

hwg_Maarten

kerberos won't help.

You should be able to give the server direct permission to the target database using DOMAIN\MACHINE$ (without the $ it doesn't work). That's still not ideal since it's only single tiered security through sharepoint instead of two tiered through sql and sharepoint.




Re: SharePoint - Business Data Catalog Unable to connect ...

Ashley76

hi,

can u please tell me how to set DOMAIN\MACHINE$ in sharepoint.

thanks.





Re: SharePoint - Business Data Catalog Unable to connect ...

hwg_Maarten

you do it through enterprise manager in sql not in sharepoint...