Network

K2 has SharePoint Designer installed.

SQL Server security

A database: vanarts is created. And a table:Students is created.

Connection to SQL Server database can be created in SharePoint Designer.

How to expose the data from the Database Connections?

SharePoint Designer 2010

Open a sitepage: Home.aspx and click "Edit File" button in the Ribon

You can insert a Data View control or Data Form control into [P] tag box.

SharePoint Designer 2010

Open "http://k2/sites/data" collection

SQL Server Security mode:Windows Authentication mode

Cannot create a connection to SQL Server from SharePoint Designer

Server Error: An error occurred while retrieving the list of Databases from k2:An anthentication error occurred. Your logon information may be incorrectly enter, you may not have permission to access this data source, or the requested authentication method may not be supported.

The Single-Sign On option is a discontinued feature in SharePoint Designer 2010 and therefore should not be used to connect to a database.

Because "Use Single-On Authentication" is discontinued, to connect to SQL Server with Security set to "Windows Authentication", you must use "Use custom connection string".

Connect to a Database Server: K2 (Windows Authentication mode)

Provider=SQLOLEDB;Data Source=K2;Initial Catalog=vanarts;Integrated Security=SSPI;

SQL Server 2008 R2: Queen

Security: Windows Authentication

Database:ShareDB

Custom Connection String

Provider=SQLOLEDB;Data Source=Queen;Initial Catalog=ShareDB;Integrated Security=SSPI;

Expose the data

The Database Connections cannot be used in DataFormWebPart control. "This control does not allow connection strings with the following keywords: 'Integrated Security', 'Trusted_Connection'"

Actually, when you click on "Employee of vanarts on K2" database connection object, the error pops up.

Server error: an error occured while retrieving the list of Tables from the vanarts database: The server for the data source returned a non-specific error when trying to execute your query. Check the format and content of your query and try again.

Notes:

• If you are trying to connect to an external server running SQL Server, make sure that SQL Server authentication is enabled on that server.
• If you cannot use SQL Server authentication on the server that is running SQL Server, make sure that Single Sign-On authentication is enabled for the server where your SharePoint site resides.

Unforfunately, SharePoint 2010 gets rid of the Single Sign-On feature.

SharePoint Desinger 2010

Access denied by Business Data Connectivity

SharePoint Central Administration

Open "Business Data Connectivity Service"

Set Object Permissions

Then, Administrator can view the external list: Queenlist from K2 computer--The SharePoint Server computer. But if accessing the Queenlist from Queen computer or K1 computer, Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

See Kerberos delegation

Microsoft Business Connectivity Services can use Secure Store to map the user's credentials to a set of credentials for an external system. You can either map each user's credentials to a unique account on the external system or you can map a set of authenticated users to a single group account.

Target Applications

A target application is a collection of information that maps a user or users to a set of encrypted credentials stored in the Secure Store database. Each Target Application has a unique APPLICATION ID that you define that is used to reference the target application from external applications such as EXCEL Services or SharePoint Designer.

Configuring Secure Store Service

Register a managed account

SharePoint Central Administration--Security--General Security--Configure managed accounts

Create a 'Secure Store Service' service application

Service Application Name:Kaiming Secure Store Service

DataBase Server: K2\SharePoint

Database Name: Kaiming_Secure_Store_Service

Database Authentication: Windows Authentication

Application Pool Name:SecureStore

Select a security account for this application pool:

Configurable:AAA\jim

Click OK

Open "Kaiming Secure Store Service"

Generate a new encryption key

Click "Refresh Key" button

Store credentials in Secure Store

Create a target application

Set credentials for a target application

These credentials are used by the calling application to provide access to an external data source. If the target application is of type Individual, you can also enable uses to supply their own credentials.

Once the credentials for the target application is set, it is ready to be used by Business Connectivity Services.