Recently, I have done a lot of work with SharePoint Designer 2013 and External Content Types (ECT). In a previous blog, SharePoint Online to Azure SQL Database: Part 1, I covered how to work with ECTs. Since, writing that blog, I ran into an unexpected issue that caught me by surprise. I was adding a data source for my ECTs in a new environment and made a mistake when entering the credentials for the Secure Store Application ID. This blog is not meant to be long or mind blowing. It is meant to save you some time. The following is what to do if you enter the wrong credentials for the Secure Store Application ID in SharePoint Designer 2013.
When I was setting up the data source for the Secure Store Application ID, I entered everything correctly to start:
Figure 1. SQL Server Connection Strings
After I entered the information and clicked “OK”, a windows dialog opened requiring the credentials for the Application ID in the Secure Store:
Figure 2. Windows Authentication Dialog
If you read section 6, External Content Types in SharePoint Online to Azure SQL Database: Part 1, you will see the steps for adding an external data source for ECTS. In my own writing, I warned about how to enter the credentials and to ensure that the credentials were entered in the [Server admin login]@[server name] format. However, I was rushing and entered the database name for the server name. I clicked “OK” and waited for the good news. Instead, SharePoint Designer presented an error message stating that the login failed. At that point SharePoint Designer 2013 would not allow me to fix my mistake. After a couple attempts to clear my cache and restart SharePoint Designer 2013, I realized that the credentials were not cached in SharePoint Designer. They had to be somewhere else.
Editing Windows Credentials
I went looking for the credentials in Windows. I opened Windows Credential Manager in Windows 10, and first looked under Web Credentials. Not finding what I was looking for I opened the Windows Credentials:
Figure 3. Windows Credentials
I scrolled through and found a credential set, which has the same name as my Secure Store Application Target ID. I expanded the credentials and then clicked “Edit”:
Figure 4. Windows Credentials for Secure Store Target Application
Figure 5. Edit Generic Window Credentials
Once I edited the credentials and saved them, I went back to SharePoint Designer 2013 and the external data source successfully authenticated with the database.
Like I mentioned at the beginning of this post, it wasn’t meant to be long or mind blowing. It was meant to save you time. If you have found this post, you have no doubt found many other blog posts informing you to clear your cache with SharePoint Designer, setting some options, then restarting SharePoint Designer. Admittedly, that often fixes SharePoint Designer, but it won’t fix your problem if you’ve entered the wrong credentials for an external data source setup to connect with a secure store application ID. I hope this blog has given you the answer you are seeking and saved you some time searching and perhaps some trial and error along the way.