4

I am experimenting with SharePoint Designer 2010 to create various data sources. When creating database connection using SQL user id and pwd, i get a prompt saying that the user id and pwd will be stored in clear text.

I created a connection to the Northwind database, selected a table and saved the connection. I then created a webpart page, inserted data source and display the data on the web part page.

My questions are-

Which file actually stores the user id and pwd details and where is this file located?

Where are the configured Database connections or rather all the Data sources (database, rest, xml, soap) physically stored?

In Data Sources section in SP Designer I see lists, libraries, and Database Connections. I do not see any library in the sharepoint portal that contains this information.

Improve this question

2 Answers 2

Reset to default
1

TZHX is incorrect but I lack the reputation on this exchange to downvote him. The Secure Store is NOT used and neither is the Business Data Catalog. There are ways to use these service applications to securely connect but that's not what the OP was doing. He was creating Data Sources straight in SharePoint Designer.

Actually, the Data Sources that you create using SharePoint Designer are stored under (All Files)/_catalogs/fpdatasources as XML files. If you entered the username and password then it's going to get stored in clear text in this XML file.

It will look something like this

<udc:DataSource xmlns:udc="http://schemas.microsoft.com/data/udc"
MajorVersion="2" MinorVersion="0"><udc:Name>DATABASENAME on
DATABASESERVER </udc:Name><udc:ConnectionInfo><DataSourceControl><![CDATA[<asp:SqlDataSource
id="SqlDataSource1" runat="server" __designer:commandsync="true"
ProviderName="System.Data.SqlClient" ConnectionString="Data
Source=DATABASENAME;User ID=USERNAME;Password=PASSWORD;Initial
Catalog=AAHOLD;" SelectCommand="SELECT * FROM [DATABASENAME] "
/>]]></DataSourceControl></udc:ConnectionInfo><udc:Type
MajorVersion="1" MinorVersion="0" Type="Sql"/></udc:DataSource>
Improve this answer
3
  • Thanks for a response. I never really investigated this further since the data we finally connected to back then did not require any special security access. I will look into this once again when i can and will provide an update (but it does seem you got the right location).
    – Ron
    Oct 18, 2014 at 12:23
  • I just tested this recently. The credentials are stored in clear text in the location you mentioned. While this file does not seem accessible via the web site interface, it can be accessed via SP Designer.
    – Ron
    Feb 15, 2015 at 3:13
  • Yes, I think you are right about not being able to access that file via HTTP. The best practice is to use the secure store so that the credentials aren't stored in plain text anywhere.
    – Derek Evermore
    Feb 19, 2015 at 21:29
0

The credentials are stored using the Secure Store Service. These are held in the _SecureStoreDB on the database server that is supporting your SharePoint installation. They are not stored in a traditional file (very little about SharePoint configuration is file-based).

The credentials themselves are stored as an "image" datatype in SQL server (in the SSOCredentials table of the above named database). IIRC, this is just a way of it labelling a small binary chunk; but it abstracts the method of encryption that they're using. Obviously, it will have to be a symmetric means.

The data sources are stored using the Business Data Catalog model, which is described in the _BusinsesDataCatalogDB database. There are a handful of linked tables, but going into the details isn't really worthwhile. Your means of interaction with the model should never touch this DB, and should be done either by uploading BDC models as solutions; or through SP Designer as you've been doing.

Improve this answer
2
  • I looked up the SecureStoreDB. There is a table with the name SSSCredentials instead of SSOCredentials and it is empty. I wanted to understand how are the credentials stored as plain text and how can it be a potential security risk. The link i am following is office.microsoft.com/en-us/sharepoint-designer-help/… It mentions "SQL authentication saves the user name and password as text in the data connection, any user with permission to open the site in SharePoint Designer 2010 can view these credentials."
    – Ron
    Aug 13, 2012 at 8:42
  • Any other hints on this? I am still trying to understand how the connection string is exposed in clear test if it is stored in SecureStore DB.
    – Ron
    Aug 23, 2012 at 10:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.