January 13, 2009 at 11:52 am
Well,
So far I cannot seem to find a way to get SQL Server to see anything besides it's own little universe.
Our network has two servers:
CORDEFSDB1
CORDEFSFP1
DB1 is the Database Server
FP1 is the Domain Controller (with file shares).
We've installed the SQL Server to the DB1 server, and I've setup all the services to use the
CORBIPLASTICS\sqlservice
Domain Account (through active directory or something like that) which is a member of the Pooling and Domain Users groups. I've configured everything to work the way that it should to allow adhoc queries for Excel file access.
Now, I've updated the setup to have the TMP and TEMP env vars for sqlservice account to point to a full access directory on the CORDEFSDB1 server, as well, I have logged into the SQL Server both locally (on the actual cordefsdb1) with Management Studio, and from my system using my own local copy of Management Studio to lilnk to the CORDEFSDB1\SQLCORBI server, and run the following ad-hoc query:
select *
from
openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;database=\\cordefsfp1\shared\Pooling\Private\Rexam\BOL booking\Rex_Errors_010409.xls',
[Sheet1$]);
it works in both occasions, ONLY WHEN I am logged in as "sa". WHen I run this locally on the CORDEFSDB1 computer, or when i connect via my computer with my Windows Authentication account (as opposed to sa):
execute as login='CorbiPlastics\jruiner';
select *
from
openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;database=\\cordefsfp1\Shared\Pooling\Private\Rexam\BOL booking\Rex_Autobook_122908.xls',
[Sheet1$]);
revert;[/qode]
I get this error response:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 8
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
As a QUick test, I also tried this, since E: is a Local drive for the CORDEFSDB1 server:
execute as login='CorbiPlastics\jruiner';
select *
from
openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;database=E:\SQL_BOL\MSSQL\Rex_Errors_010409.xls',
[Sheet1$]);
And I received the same error.
What am I missing? Why does the sa account have a unfettered access to work with the share drives and open these excel files, while my Windows Authenticated Account Doesn't?
The funny thing is, I am the DB Admin, so my WinAuth account is listed as a "sysadmin" server role.
Please Help, because I really need this to be fully functional as the application I've developed requires the Ad-Hoc querying, and so far I haven't been able to get it to work outside of a locally installed server...
Additionally (and perhaps it is wishful thinking) but on every system in our network the "S:" map is mapped automatically to the CORDEFSFP1\Shared file share, and yet SQL server seems to demand that i use the UNC naming.
THanks
Jaeden "Sifo Dyas" al'Raec Ruiner
January 13, 2009 at 12:06 pm
Check "select user" when logged in with your Windows account. If it comes back as something other than "dbo", then that might be the situation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2009 at 2:39 pm
Nope. Came back as "dbo" just fine.
Okay, so after several tweaks and restarts, I have got the Ad-Hoc completely working for any SQL Authorization Account, and will access every file on the network shares as it should.
However, when I run the exact same query using Windows Authorization I now get this error:
Msg 7399, Level 16, State 1, Line 8
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 8
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
I am executing these queries from Management Studio on my Lap Top, as it is connected to the CORDEFSDB1 server. so the only difference right now is SQL Auth versus Win Auth so that's where the issue has to exist.
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner
January 14, 2009 at 6:52 am
I'm used to seeing that particular error when the file is already open, usually on my own screen, right behind Management Studio. I'm assuming that's not the case here.
Does the Windows account you're using have access rights to the folder the file is in?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 15, 2009 at 8:54 am
Yes.
The two Servers are a Domain Controller (and File Server) and the DB server, both running 2003. My Domain Login has access to all the files and directories in place. I have noticed that the Ad-Hoc queries do not entertain Active Directory Drive Mappings, so even though every system has S: mapped to \\CORDEFSFP1\Shared, I can't use S: in the Ad-Hoc query.
Basically this is what I did:
select *
from
OpenRowSet('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\CORDEFSDB1\SQL_BOL\MSSQL\Rex_Errors_010409.xls',
[Sheet1$]);
--OpenRowSet('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\CORDEFSFP1\Shared\Pooling\Private\Rexam\BOL booking\Rex_Errors_010409.xls',
[Sheet1$]);
--OpenRowSet('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\CORDEFW042\db\Rex_Errors_010409.xls',
[Sheet1$]);
Key: ( i kept the order the same as the above From statements for reference)
Now successively, I would run the above query, which you can see points to the same file in different locations, one for each Drive. On my Laptop (#3) I use Management Studio to Log into the DB Server's SQL Server (#1). I use Windows Authorization using my built in CORBIPLASTICS Domain AD User Account.
In succession I run the Above Query uncommenting and commenting the appropriate lines to test each share location.
#1 Worked successfully, but #2 and #3 failed for my Windows Account.
I then tell the query to Change connection, reconnecting that query window in Management Studio to use my 'sk' SQL Authorization account.
#1, #2, #3 all worked perfectly, no issues.
the Windows Account is listed as a SQL Server 'sysadmin' for the SQL Server, while the 'sk' account is only a 'dbcreator', and I have tried this test with other SQL accounts that are even less privileged.
On MSDN, someone has suggested that I set up a SPN (Service Principle Name) for the SQL Server's Services Account Log on, as I am using a Domain User Account to run the SQL Server Services. As well I was directed to verify that the Active Directory account for the Services' Log ins should be updated to "Trust for Delegation". Both of these settings are unverifiable at the current time, until my Domain Admin comes in to have a look, but it is my impression that they are most likely not done. Hopefully, those two facets of setting up SQL Server correctly will resolve my issue.
If not, I will most definitely be back.
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner
March 3, 2009 at 10:28 am
Hi, Jaeden
Have you ended to resolve your issue? Recently I also encountered identical issue as yours that SQL authentication account can access linked server Excel file successful but not for the sys admin level Window authentication window account.
Hopefully I can learn from your experience to resolve my issue.
Thanks
Eugene
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply