Access to SQL DB using External Content Type generates SQL error status 6
-
2011年5月19日 下午 07:02
Been working on this for days! I created an External Content Type (ECT) for a SQL 2008 database using SharePoint Designer. Then created a Target Application using Central Admin to store credentials in the Secure Store. When I try to access the ECT , the SQL server generates the following error:
Login failed for user '<domain>\<User_Account>'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: 10.10.100.15]
I have verified that the SQL server uses Mixed Authentication. The <User_Account> I used is has SQL sysadmin role. I get the same error on two separate SQL servers with two different databases. Any assistance will be greatelky appreciated.
MosesMusa
所有回覆
-
2011年5月20日 下午 12:16
Hi,
Did you enter <domain>\<User_Account> as "User Name" when you set the values for the credential fields that are defined for the Secure Store Target Application?
Are you able to connect to MS SQL through SQL Server Management Studio using <domain>\<User_Account> and SQL Server Authentication?
Dmitry
Lightning
Tools Check out our SharePoint tools and web parts- 已標示為解答 Lily Wu 2011年6月1日 上午 02:29
-
2012年5月14日 上午 01:52
I am having the same error (event id 18456) when trying to configure a Database connection or trying to do an ECT.
Have three SQL databases: sharepoint one for the farm (single server farm now, one same server of course); application SQL database, to be fleshed out (dummy table defined now) and ultimately the one I want to hook to), and MSSQLSERVER database that I didn't actually create, but maybe from report server as a side effect? This is the one that seems to puke when I do a database connection.
When I try and use ECT I get two events
18456 as above with Client: <localmachine>
8080 Business connectivy services
Could not open connection using 'data source=<computername>; initial catalog=<SQLName>;integrated security=SSPI;persist security info=false;pooling=false' in App Domain <long string>. The full exception text is: Cannot open database <SQLName> requested by the login. The login failed. Login failed for user '<domain>\<user>.
That user has admin login for all of the databases, all set for windows and SQL authentication, can run SQL Management Studio as that user and make changes to the SQL settings, add tables, etc.
Really frustrating and this is holding up development work. Is it really this hard? I have seen a lot of postings relative to this, but never a solution.
Hopefully, if you respond, realize some of us may not fully grasp the context of what you suggest, so if you can a bit of step by step for those of us who are challenged!
Thanks in advance.
Larry Heimendinger TechEvents.com
-
2012年5月14日 上午 01:53Can you elaborate on Secure Store Target Application?
Larry Heimendinger TechEvents.com
-
2012年5月16日 下午 12:50
Hi Larry,
Regarding Secure Store please take a look at these articles:
- Setting up BCS with Secure Store Application impersonation
- BCS Secure Store Services
- Secure Store Service
Are you able to connect to MS SQL server from Visual Studio->Server Explorer?
Dmitry
Lightning
Tools Check out our SharePoint tools and web parts |
Lightning Tools Blog -
2012年5月16日 下午 04:06
To the Visual Studio question, the answer is no. Maybe I am doing something incorrectly or worse due to lack of knowledge.
From Tools,I choose Connect to Database and get an Add Connection dialogue box. If I choose Microsoft SQL Server as the data source, I can use the drop down box to get to the server and database name, and in the database part, I can select master. The connection is added. But it is not to the tables I want in the database.So I tried Microsoft SQL server Database file as the data source and used server-name\database name for the database file name, but apparently it wants a path to the file.
So I browsed to c:\program files\Microsoft SQL Server\MSSQL10_50.<databasename>\MSSQL\Data\<databasename>.mdf. If I click OK, nothing happens. If I click Advanced, it goes to not responding and then I get an error "Network-related or instance-specific error occured while establishing connection to SQL Server. The server was not found or was not accessible.... Error:26 error locating server/instance specified" Same error appears from test connection.
However, no problem at all accessing this from SQL Management Studio.
Is this really supposed to be so hard?
Larry Heimendinger TechEvents.com
-
2012年5月16日 下午 07:58
Are able to see any other databases beside master? Do you use Windows or SQL authentication?
Dmitry
Lightning
Tools Check out our SharePoint tools and web parts |
Lightning Tools Blog -
2012年5月17日 上午 03:05
Thanks for that image. Yes, I can connect to the database now.
Given the above connection, can you help me translate that to SPD? Here is what I get from External Content Type attempt to add a new connection. Similar authentication errors from Data Sources cannot get any databases.
Continued in next msgLarry Heimendinger TechEvents.com
-
2012年5月17日 上午 03:06
I also tried several varients on credentials like
Database server rm-sharepoint\salesorder
Database name salesorder_data and salesorder_data.dbo
And various other combintations, but no luck -same error.
Ireally appreciate your help and patience.Larry Heimendinger TechEvents.com
-
2012年5月17日 上午 08:09
It's strange because these values should work(the same values as in Visual Studio)
- Database server: rm-sharepoint\salesorder
- Database name: salesorder_data
Is your Visual Studio running under this user RMKIRKLAND\rm-netadmin?
Dmitry
Lightning
Tools Check out our SharePoint tools and web parts |
Lightning Tools Blog -
2012年5月17日 下午 03:16
Certainly correct. If you notice on the two screen shots I sent from SPD, I connected with user identity and that user was the one reported.
I am getting events in the error log:
Whatever the login combo I choose, it is preceded by this error
Obviously I am doing something right in that I can connect via Management Studio and Visual Studio but something wrong with SPD. Thanks again for your help.
Larry Heimendinger TechEvents.com
-
2012年5月17日 下午 08:44
As I see in the log file your datasource is rm-sharepoint and initial catalog is salesorder. But your successful connection from Visual Studio was with another values. datasource was rm-sharepoint\salesorder , initial catalog was salesorder_data. Did you try exact these values? Also it seems (on your Visual Studio screen shot) that database name has whitespace salesorder _data (before underscore symbol). Is it true or not? If it really has whitespace you should enter database name with whitespace in SDP.
Please post here screen shot from MS SQL management studio. Take a look at my ones:
Dmitry
Lightning
Tools Check out our SharePoint tools and web parts |
Lightning Tools Blog
- 已編輯 Dmitry KaloshinMicrosoft Community Contributor 2012年5月17日 下午 08:51
-
2012年5月18日 上午 03:00
Okay, I got it to work, finally. Database server rm-sharepoint\salesorder and database name saldesorder_data. I had never tried this combination before.
Something I don't fully understand. If I go to Data Sources and click Database Copnnection, here is what I get
then
Shouldn't this give me a list of databases and tables?
Larry Heimendinger TechEvents.com
-
2012年5月18日 上午 03:16
Now here is the damnest thing. I restarted SDP and tried to connect again as above in ECT and it is refusing to do so, giving me the error I reported earlier today as Cannot open database "Salesorder_data" as requested by the login. The tlogin failed. Login failed for user "RMKIRKLAND\rm-netadmin."
Interestingly, I can connect to database sharepoint which is the config and content database for SP itself. Here is the view from SQL Server Management Studio for both databases........
Larry Heimendinger TechEvents.com
-
2012年5月18日 上午 03:46
Okay, ignore the can't connect. Since I only had one table defined so far, I simply deleted salesorder_data and created a new database, and I got that to open.But the question remains about Data Sources...
Larry Heimendinger TechEvents.com
-
2012年5月18日 上午 09:39
You should use custom connection string. For example my connection string is Data Source=perseus\sharepoint;Initial Catalog=Northwind;Integrated Security=SSPI;Provider=SQLOLEDB. Your connection string should look like this Data Source=rm-sharepoint\salesorder;Initial Catalog=saldesorder_data;Integrated Security=SSPI;Provider=SQLOLEDB
Dmitry
Lightning
Tools Check out our SharePoint tools and web parts |
Lightning Tools Blog- 已提議為解答 LMHeimendinger 2012年5月18日 下午 09:19
-
2012年5月18日 下午 09:22
I was able to connect to a single SQL instance (SALESORDER) and browse the databases inside it.
Who makes this stuff up? you can't connect to a database with a simple Windows authentication? Really? I am going to contact some folks a few miles away in Redmond and offer a piece of my mind....
Larry Heimendinger TechEvents.com
-
2012年7月31日 下午 05:18
I've tried using this connection string:
but I still get an error:
Please help me in resolving this issue.
.png)
