Does SQL connection using Windows Authentication work in SharePoint Designer 2013?

I tried connecting to a SQL DB by creating a new Data Connection with the following parameter:

1. Server Information
Server Name: <SQL Server Name>
Provider Name: Microsoft .Net Framework Data Provider for SQL Server

2. Authentication
(Save this username and password in the data connection)
User name: <My Windows User name>
Password: <My Password>

I get the following error:

Server Error: An error occurred while retrieving the list of Databases from <SQL DB Name>: The data retrieval service encountered an error during connection to the data source. Contact the server administrator for more information.

I know that my windows authentication works on the SQL DB because I'm able to connect to it using the data connection in Excel desktop client.

Also, I tried using SQL authentication test accounts, but that did not work either...

Either I'm doing a simple mistake or this connection is not possible at all. Any pointers to troubleshoot this issue?

I've also tried getting the connection string from Excel and tried using the same, but that did not work either.

Hi Arut,

Below is the paragraph that is extracted from the book:

When connecting to a SQL Server database you cannot use Windows authentication or the SharePoint Server single sign-on service, named Secure Store Service(SSS). You are limited to using a SQL Server authentication user name and password, which are sent over the network in plain text.

So Windows authentication users are not supported to connect to SQL Server databases in SharePoint Designer.

As the error still occurs when you connect to SQL server with SQL server authentication users, I recommend to check if the SQL Server authentication method is set to be SQL Server and Windows Authentication mode and please make sure that the account has permission to access SQL Server databases.

Best regards.

Victoria

Hi Arut,

Below is the paragraph that is extracted from the book:

When connecting to a SQL Server database you cannot use Windows authentication or the SharePoint Server single sign-on service, named Secure Store Service(SSS). You are limited to using a SQL Server authentication user name and password, which are sent over the network in plain text.

So Windows authentication users are not supported to connect to SQL Server databases in SharePoint Designer.

As the error still occurs when you connect to SQL server with SQL server authentication users, I recommend to check if the SQL Server authentication method is set to be SQL Server and Windows Authentication mode and please make sure that the account has permission to access SQL Server databases.

Best regards,

Vi

Victoria,

Thanks for your response.

I did try SQL authentication yesterday before posting this question.

In SharePoint Designer 2013, there is no way to specify SQL Authentication explicitly, other than using Custom Connection String (which is ironic for 2 reasons: first being that the default is only Window Authentication or Single Sign-On; and the second being I'm not sure what is the authentication mechanism really being considered under the hood when I pass the custom connection string)

I tried the following connection string:

Provider=SQLOLEDB.1;Persist Security Info=True;User ID=test;Password=<Password>;Initial Catalog=<DB Name>;Data Source=<DB Server Name>;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=<My PC Name>;Use Encryption for Data=False;Tag with column collation when possible=False

and got the following error:

I know that there is nothing wrong with the custom connection string because I'm able to connect using the same from Excel.

Hi Arut,

I recommend to type the SQL account and its password directly in the Authentication user name and password instead of using the connection string to see if the issue still occurs.

Before doing that, please make sure that the user can access databases from SQL Server Management Studio.

Best regards.

Victoria

Hi Arut,

I recommend to type the SQL account and its password directly in the Authentication user name and password instead of using the connection string to see if the issue still occurs.

Before doing that, please make sure that the user can access databases from SQL Server Management Studio.

Best regards,

Vi

Hi Arut,

How is everything?

Is there anything update about this issue?

If you have any questions, please feel free to let me know.

Best regards,

Victoria