Solved

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

Posted on 2009-07-10
18
1,131 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
  • 10
  • 6
  • 2
18 Comments
 
LVL 42

Expert Comment

by:zephyr_hex
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 500 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
 

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 42

Expert Comment

by:zephyr_hex
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now