| Author |
Topic |
|
learning_grsql
Posting Yak Master
230 Posts |
 Posted - 2012-10-15 : 18:07:10
|
Hi,I have connected a excel file as a linked server in sql server 2005 to import data from the excel sheet to sql server.When I run the following code in Management studio connected using windows authentication, it works fine.select * from linkserver...sheet1$ But when I run the same code in Management studio connected using sql server authentication user "sa", I get the following error :The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "exportlink" reported an error. The provider did not give any information about the error.Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "linkedserver". |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-15 : 22:25:52
|
| did you change connection string? for sql login you need to pass username password as its not trusted connection------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-10-16 : 01:23:33
|
| @visakh16, How can I change it? I don't see any option to put username and password in the properties of linked server. Even I tried to create one, I could not find a window to put username and password |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-10-16 : 01:38:59
|
| In SSMS|Server Objects|Linked Servers|<myLinkedServer>|Right Click and Select security.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-10-16 : 02:34:25
|
| Thanks...But in security I have option ticked "not be made without security context" . The other options available are :"be made""be made with current security context""be made using the security context".I tried changing options in all of them but still the same. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-10-16 : 10:07:57
|
| still shows the following error The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "linkserver" reported an error. Authentication failed.Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "linkserver". |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-16 : 23:12:11
|
| are you having .xslx or .xls file?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-10-17 : 05:34:58
|
| A couple of other ideas to check:1) Is the workbook open?2) Firewall ?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-10-17 : 08:56:56
|
| Thank you.It was .xlsx. Yes I already tried every possible thing like firewall disabled, workbook closed but nothing worked.As I'm able to achieve the result using windows authentication, I think I shall move on. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-17 : 22:47:51
|
quote:
Originally posted by learning_grsql
Thank you.It was .xlsx. Yes I already tried every possible thing like firewall disabled, workbook closed but nothing worked.As I'm able to achieve the result using windows authentication, I think I shall move on.
Ok. But sounds a little strange though why sql login doesnt work------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
link server cannot run using "sa" account