Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-07-10
18
Medium Priority
?
1,158 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
0
Comment
Question by:taverny
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 2
18 Comments
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 24825312
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.
0
 

Author Comment

by:taverny
ID: 24825350
Thanks for your response, where do I see this log?
0
 
LVL 11

Accepted Solution

by:
Andrei Teodorescu earned 2000 total points
ID: 24825797
step-by-step SSO in SHP Designer: http://blogs.msdn.com/sharepointdesigner/archive/2007/08/27/an-introduction-to-single-sign-on-sso-with-data-views.aspx

open SQL Management Studio, conect to the server, expand Management, expand SQL Server Logs, look into the first one.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:taverny
ID: 24826115
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.
0
 

Author Comment

by:taverny
ID: 24826187
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?
0
 
LVL 11

Expert Comment

by:Andrei Teodorescu
ID: 24826202
:)

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

Author Comment

by:taverny
ID: 24826229
Do I do it on the SQL server or my sharepoint ?
0
 
LVL 11

Expert Comment

by:Andrei Teodorescu
ID: 24826241
read the article from the link above, it is detailed step-by-step (from the CA site of your sharepoint production server)
0
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 24826325
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.
0
 

Author Comment

by:taverny
ID: 24826391
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?
0
 

Author Comment

by:taverny
ID: 24826401
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?
0
 

Author Comment

by:taverny
ID: 24826548
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.
0
 
LVL 11

Expert Comment

by:Andrei Teodorescu
ID: 24826854
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
0
 

Author Comment

by:taverny
ID: 24826953
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?
0
 
LVL 11

Expert Comment

by:Andrei Teodorescu
ID: 24827182
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.
0
 
LVL 11

Expert Comment

by:Andrei Teodorescu
ID: 24827186
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.
0
 

Author Comment

by:taverny
ID: 24827220
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.
0
 

Author Closing Comment

by:taverny
ID: 31602147
Thank you .
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question