We help IT Professionals succeed at work.

I can't connect to my SQL DATABASE from Sharepoint Designer

taverny
taverny asked
on
1,239 Views
Last Modified: 2012-05-07
Hi Experts,
I am trying to connect to my SQL 2005 database using Single Sign-On authentication form Sharepoint Designer. I know my username and password but for some reason it doesn't let me connect this is the error that I get:
Server Erro: An error occured while retrieving the list of Databases from XXXXX: An authentication error occured. Your logon information may be inccorectly entered, you may not have permission to access this data source, or the requested authentication method may not be supported. Contact the server administrator for more information.

If I try to connect using the other method provided ( save the username and password in the data connection ) it works fine.

Why can't I connect to my SQL Database and how can I resolve it.
Thanks
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
check your SQL database log for a corresponding authentication error.  it should give you a "state" code.  one common error is that SQL is expecting SQL authentication and is being given windows authentication...

the state code will clarify.

Author

Commented:
Thanks for your response, where do I see this log?
Business Owner
CERTIFIED EXPERT
Top Expert 2006
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
ok this is the error that I get:
07/10/2009 13:32:13,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 192.168.1.9]
07/10/2009 13:32:13,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.

Author

Commented:
well, I am looking at my test server and I do have an SSO database , on my production server I don't see it , and I really don't remember creating this Database . Is the problem comming from that?
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
:)

so, you need to enable SSO on the production server first

Author

Commented:
Do I do it on the SQL server or my sharepoint ?
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
read the article from the link above, it is detailed step-by-step (from the CA site of your sharepoint production server)
CERTIFIED EXPERT
Top Expert 2010

Commented:
the easiest way to check SQL logs is to use SQL Management Studio to connect to the SQL instance.  then go to the Management section and review the log.

Author

Commented:
I am currently following the step on the link you sent me, but when enterring the account names on both places under the manage server settings for single sign-on , I get an red error on the top that says :
" you do not have the rights to perform this operation" , what am I doing wrong?

Author

Commented:
never mind I type the administrator username on both boxes and it took it. Is that ok to use the Administrator password for the sign on?

Author

Commented:
ok, well I am stock now, I am at the page "Manage Account Information for an Enterprise Application Definition " 
what should I put as the Group account name?
is it the administrator username or do I need to create a a new group just for that. I am not really sure what this window does.
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
here is a more detailed step-by-step tutorial about setting-up SSO:
http://www.thorprojects.com/blog/archive/2008/08/02/moss-single-sign-on-setup-step-by-step.aspx

Author

Commented:
Thanks for the other post , but can you explain me what are those users for.
I am on the last step on the first Doc : step 6. and it ask me for a group name . is it the group name of who has access to run the connection or the group that is allowed to create their own connection. I will mainly be the only one who customize sharepoint so I don't need anyone else to create link , but I want everyone to be able to see the data view that I will be creating from those connection. then on the very last step it ask me for a username and password is a username and password that I use to connect to SQL or a new one?
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
the group/individual allowed to access the application
and it depends on what you have set up on step 5

from: http://technet.microsoft.com/en-us/library/cc263178.aspx
In the Account type section, select one of the following:

Group. Select this option if users will connect to the enterprise application through a group account. If you select this option, you need to configure account information for the application definition.

Individual. Select this option if each user has an account in the application definition.

Group using restricted account. Select this option if users will connect to the enterprise application through a group that uses a restricted account. If you select this option, credentials are stored separately for regular credentials and a different API is used to access the credentials. Select this option only when all of the following is true:
a. The account is a group account.
b. An intermediary application such as Business Data Catalog imposes further security restrictions.
c. The data is highly sensitive.
Andrei TeodorescuBusiness Owner
CERTIFIED EXPERT
Top Expert 2006

Commented:
more detailed explanations (http://209.85.129.132/search?q=cache:lse8XFLO9CkJ:www.brettlonsdale.com/Shared%2520Documents/BDC-SSO%2520WhitePaper.pdf+manage+account+information+for+an+enterprise+application+definition&cd=7&hl=en&ct=clnk) - the pdf is no longer available:

a. Group  Select Group if you want a Domain Group to access the database using a particular account. E.g. If you want your Sales department to access the database asone user then choose this option. You will then be able to map the credentials for that group such as: DOMAINNAME\Sales -> DOMAINNAME\SalesUser. The SalesUser will have the permissions on the database tables that are required by salespeople. E.g. The DOMAINNAME\Sales group may have Read Permissions on the Customers, Orders, Order Details tables but no permissions on the Suppliers table.

b. Individual  Select individual if you want to map the credentials for a User Account to another user Account. For example: DOMAINNAME\Brett -> DOMAINNAME\Administrator. When using the Data Form Web Part, If the user doesnt have stored credentials when trying to access the database, they will be prompted the first time, and then Credentials will be stored for them so that theyare not prompted again.

c. Group using restricted account  Choose this option if you are going to use a group such as DOMAINNAME\Domain Users so that all users will be able to accessthe database via SSO. The group name will still access the database with a specificuser account. This option uses a different API to the other two options to access thedatabase. It is worth noting that SharePoint Designer and Excel Services do not support this option. Use this option when using BDC that applies further trimmingof security so that a security breech doesnt occur using a privileged account.

Author

Commented:
Perfect I got it. Thanks again now it makes sense, at the moment I am only pulling data from my SQL I am not pushing anything , so I should be fine with my domain users.

Author

Commented:
Thank you .
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.