Here is my setup, I'm setting up several Access .mdb's as linked servers.
The .mdb's are not password protected and they reside on our network. I can
get them in Sql server fine, and when I logon using my domain
username/password, I can get to them without any problem.
What I'm trying to do is setup a generic sql user who can access the linked
servers, but I keep on getting the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Cannot start your application. The
workgroup information file is missing or opened exclusively by another
user.]
I would like to use just a sql user to access the files like "sa", but I'll
set them up using my domain username/password if need be.
What am I missing?
Thanks,
BarryAs long as the "MS SQL Server" service's logon account has access to the UNC
where your files are located, you will make the connection using the linked
server security option "Be made without using a security context." Also,
just in case, check the default open mode and default record locking mode
for both the access db's.
They should be Shared, No Locks respectively.
Tools > Optons > Advanced Tab
Christopher Winn
Database Administrator
Fort Bend ISD
"Barry" <no_one@.home.net> wrote in message
news:OvAmixmFEHA.628@.TK2MSFTNGP10.phx.gbl...
> Here is my setup, I'm setting up several Access .mdb's as linked servers.
> The .mdb's are not password protected and they reside on our network. I
can
> get them in Sql server fine, and when I logon using my domain
> username/password, I can get to them without any problem.
> What I'm trying to do is setup a generic sql user who can access the
linked
> servers, but I keep on getting the error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Authentication
> failed.
> [OLE/DB provider returned message: Cannot start your application. The
> workgroup information file is missing or opened exclusively by another
> user.]
> I would like to use just a sql user to access the files like "sa", but
I'll
> set them up using my domain username/password if need be.
> What am I missing?
> Thanks,
> Barry
>|||Thanks Chris, that did it.
Barry
"Winn, Chris" <seawinn@.hotmail.com> wrote in message
news:OPiqusnFEHA.2416@.TK2MSFTNGP12.phx.gbl...
> As long as the "MS SQL Server" service's logon account has access to the
UNC
> where your files are located, you will make the connection using the
linked
> server security option "Be made without using a security context." Also,
> just in case, check the default open mode and default record locking mode
> for both the access db's.
> They should be Shared, No Locks respectively.
> Tools > Optons > Advanced Tab
> Christopher Winn
> Database Administrator
> Fort Bend ISD
>
> "Barry" <no_one@.home.net> wrote in message
> news:OvAmixmFEHA.628@.TK2MSFTNGP10.phx.gbl...
servers.
> can
> linked
> Authentication
> I'll
>
Showing posts with label setting. Show all posts
Showing posts with label setting. Show all posts
Friday, March 23, 2012
Friday, March 9, 2012
Linked Server Between Domains
All
How do I go about setting up a linked server between a SQL Server 2000
instance in Domain1 to a SQL Server 2000 instance in Domain2. There is a
trust between the two domains and when I log onto the server in Domain1 I can
connect to the server in Domain2.
Any assistance would be appreciated.
ThanksHi David
If you wish to use windows authentication to access domain2 from domain1
then there needs to be a trust between the two domains. It may be easier to
get this working with query analyser first.
With your linked server you can use sp_addlinkedsrvlogin to map logins see
Books online or
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_24tv.asp for more.
Also make sure that msdtc is working correctly on both servers if you are to
make changes to data.
John
"David" wrote:
> All
> How do I go about setting up a linked server between a SQL Server 2000
> instance in Domain1 to a SQL Server 2000 instance in Domain2. There is a
> trust between the two domains and when I log onto the server in Domain1 I can
> connect to the server in Domain2.
> Any assistance would be appreciated.
> Thanks
How do I go about setting up a linked server between a SQL Server 2000
instance in Domain1 to a SQL Server 2000 instance in Domain2. There is a
trust between the two domains and when I log onto the server in Domain1 I can
connect to the server in Domain2.
Any assistance would be appreciated.
ThanksHi David
If you wish to use windows authentication to access domain2 from domain1
then there needs to be a trust between the two domains. It may be easier to
get this working with query analyser first.
With your linked server you can use sp_addlinkedsrvlogin to map logins see
Books online or
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_24tv.asp for more.
Also make sure that msdtc is working correctly on both servers if you are to
make changes to data.
John
"David" wrote:
> All
> How do I go about setting up a linked server between a SQL Server 2000
> instance in Domain1 to a SQL Server 2000 instance in Domain2. There is a
> trust between the two domains and when I log onto the server in Domain1 I can
> connect to the server in Domain2.
> Any assistance would be appreciated.
> Thanks
Linked Server Between Domains
All
How do I go about setting up a linked server between a SQL Server 2000
instance in Domain1 to a SQL Server 2000 instance in Domain2. There is a
trust between the two domains and when I log onto the server in Domain1 I ca
n
connect to the server in Domain2.
Any assistance would be appreciated.
ThanksHi David
If you wish to use windows authentication to access domain2 from domain1
then there needs to be a trust between the two domains. It may be easier to
get this working with query analyser first.
With your linked server you can use sp_addlinkedsrvlogin to map logins see
Books online or
ver_24tv.asp" target="_blank">http://msdn.microsoft.com/library/d...>
ver_24tv.asp for more.
Also make sure that msdtc is working correctly on both servers if you are to
make changes to data.
John
"David" wrote:
> All
> How do I go about setting up a linked server between a SQL Server 2000
> instance in Domain1 to a SQL Server 2000 instance in Domain2. There is a
> trust between the two domains and when I log onto the server in Domain1 I
can
> connect to the server in Domain2.
> Any assistance would be appreciated.
> Thanks
How do I go about setting up a linked server between a SQL Server 2000
instance in Domain1 to a SQL Server 2000 instance in Domain2. There is a
trust between the two domains and when I log onto the server in Domain1 I ca
n
connect to the server in Domain2.
Any assistance would be appreciated.
ThanksHi David
If you wish to use windows authentication to access domain2 from domain1
then there needs to be a trust between the two domains. It may be easier to
get this working with query analyser first.
With your linked server you can use sp_addlinkedsrvlogin to map logins see
Books online or
ver_24tv.asp" target="_blank">http://msdn.microsoft.com/library/d...>
ver_24tv.asp for more.
Also make sure that msdtc is working correctly on both servers if you are to
make changes to data.
John
"David" wrote:
> All
> How do I go about setting up a linked server between a SQL Server 2000
> instance in Domain1 to a SQL Server 2000 instance in Domain2. There is a
> trust between the two domains and when I log onto the server in Domain1 I
can
> connect to the server in Domain2.
> Any assistance would be appreciated.
> Thanks
Linked Server Between Domains
All
How do I go about setting up a linked server between a SQL Server 2000
instance in Domain1 to a SQL Server 2000 instance in Domain2. There is a
trust between the two domains and when I log onto the server in Domain1 I can
connect to the server in Domain2.
Any assistance would be appreciated.
Thanks
Hi David
If you wish to use windows authentication to access domain2 from domain1
then there needs to be a trust between the two domains. It may be easier to
get this working with query analyser first.
With your linked server you can use sp_addlinkedsrvlogin to map logins see
Books online or
http://msdn.microsoft.com/library/de...erver_24tv.asp for more.
Also make sure that msdtc is working correctly on both servers if you are to
make changes to data.
John
"David" wrote:
> All
> How do I go about setting up a linked server between a SQL Server 2000
> instance in Domain1 to a SQL Server 2000 instance in Domain2. There is a
> trust between the two domains and when I log onto the server in Domain1 I can
> connect to the server in Domain2.
> Any assistance would be appreciated.
> Thanks
How do I go about setting up a linked server between a SQL Server 2000
instance in Domain1 to a SQL Server 2000 instance in Domain2. There is a
trust between the two domains and when I log onto the server in Domain1 I can
connect to the server in Domain2.
Any assistance would be appreciated.
Thanks
Hi David
If you wish to use windows authentication to access domain2 from domain1
then there needs to be a trust between the two domains. It may be easier to
get this working with query analyser first.
With your linked server you can use sp_addlinkedsrvlogin to map logins see
Books online or
http://msdn.microsoft.com/library/de...erver_24tv.asp for more.
Also make sure that msdtc is working correctly on both servers if you are to
make changes to data.
John
"David" wrote:
> All
> How do I go about setting up a linked server between a SQL Server 2000
> instance in Domain1 to a SQL Server 2000 instance in Domain2. There is a
> trust between the two domains and when I log onto the server in Domain1 I can
> connect to the server in Domain2.
> Any assistance would be appreciated.
> Thanks
Wednesday, March 7, 2012
Linked Server (Secure Access Database) Problem
hi all;
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
Ansari
Could you please run the following-
dbcc traceon(7300,3604)
and then execute a query. You'll get a more detailed error message.Please
paste it here for us to review.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||hi Vikram
Thanks for your suggestion. I am sorry to say that I have written down some
incorrect information. I was using Microsoft.JET.OLEDB provider instead
Microsoft OLEDB Provider for ODBC Drivers. I have created the linked server
in following way
EXEC sp_addlinkedserver
@.server = 'CostingServer',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'ACCESS',
@.datasrc = 'C:\COSTING2.mdb'
go
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, 'ansari'
go
dbcc traceon(7300,3604)
go
select * from costingServer...materialgroup
here is the error message after runing dbcc traceon(7300,3604)
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Not a valid account name or password.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
and if I unset the database password and run the
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, null
it work fine..
And in last the error i have posted i.e
"Invalid schema or catalog specified for provider 'MSDASQL'."
is due to not supplying catalog name. if i change my query
SELECT * FROM COSTINGSERVER...MaterialGroup
to
SELECT * FROM COSTINGSERVER.[c:\costing]..MaterialGroup
it also work fines
ansari
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:m$2w#s$YEHA.3316@.cpmsftngxa06.phx.gbl...
> Could you please run the following-
> dbcc traceon(7300,3604)
> and then execute a query. You'll get a more detailed error message.Please
> paste it here for us to review.
> Cheers,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
Ansari
Could you please run the following-
dbcc traceon(7300,3604)
and then execute a query. You'll get a more detailed error message.Please
paste it here for us to review.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||hi Vikram
Thanks for your suggestion. I am sorry to say that I have written down some
incorrect information. I was using Microsoft.JET.OLEDB provider instead
Microsoft OLEDB Provider for ODBC Drivers. I have created the linked server
in following way
EXEC sp_addlinkedserver
@.server = 'CostingServer',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'ACCESS',
@.datasrc = 'C:\COSTING2.mdb'
go
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, 'ansari'
go
dbcc traceon(7300,3604)
go
select * from costingServer...materialgroup
here is the error message after runing dbcc traceon(7300,3604)
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Not a valid account name or password.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
and if I unset the database password and run the
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, null
it work fine..
And in last the error i have posted i.e
"Invalid schema or catalog specified for provider 'MSDASQL'."
is due to not supplying catalog name. if i change my query
SELECT * FROM COSTINGSERVER...MaterialGroup
to
SELECT * FROM COSTINGSERVER.[c:\costing]..MaterialGroup
it also work fines
ansari
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:m$2w#s$YEHA.3316@.cpmsftngxa06.phx.gbl...
> Could you please run the following-
> dbcc traceon(7300,3604)
> and then execute a query. You'll get a more detailed error message.Please
> paste it here for us to review.
> Cheers,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
Linked Server (Secure Access Database) Problem
hi all;
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
Ansari"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:uOnO$LPWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I m facing problem in setting linked server to Secure Access Database.. I
> have created a system DSN name "Costing" and set the user name and
password
> also. Then I created linked server using enterprise manager with
following
> property values
> Linked Server = CostingServer
> Provide name = Micorsoft OLEDB Provider for ODB Drivers
> Data Source = Costing
> when I press ok and click on "tables" icon it shows all the tables of
secure
> database... it means that connection successfull..
> but when I try to access tables using following query
> SELECT * FROM COSTINGSERVER...MaterialGroup
> it gives the following error
> "Invalid schema or catalog specified for provider 'MSDASQL'."
> but if remove password from the database all work is going fine without
any
> error...
> Any suggestion, help would be higly appreiciated in this regard..
Yes, don't bother with an Access database password. They are not only
insecure, a quick internet search will turn up a few mdb password crackers.
If you need your data secure, migrate it to SQL Server and use appropriate
security.
Steve
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
Ansari"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:uOnO$LPWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I m facing problem in setting linked server to Secure Access Database.. I
> have created a system DSN name "Costing" and set the user name and
password
> also. Then I created linked server using enterprise manager with
following
> property values
> Linked Server = CostingServer
> Provide name = Micorsoft OLEDB Provider for ODB Drivers
> Data Source = Costing
> when I press ok and click on "tables" icon it shows all the tables of
secure
> database... it means that connection successfull..
> but when I try to access tables using following query
> SELECT * FROM COSTINGSERVER...MaterialGroup
> it gives the following error
> "Invalid schema or catalog specified for provider 'MSDASQL'."
> but if remove password from the database all work is going fine without
any
> error...
> Any suggestion, help would be higly appreiciated in this regard..
Yes, don't bother with an Access database password. They are not only
insecure, a quick internet search will turn up a few mdb password crackers.
If you need your data secure, migrate it to SQL Server and use appropriate
security.
Steve
Linked Server (Secure Access Database) Problem
hi all;
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
AnsariCould you please run the following-
dbcc traceon(7300,3604)
and then execute a query. You'll get a more detailed error message.Please
paste it here for us to review.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||hi Vikram
Thanks for your suggestion. I am sorry to say that I have written down some
incorrect information. I was using Microsoft.JET.OLEDB provider instead
Microsoft OLEDB Provider for ODBC Drivers. I have created the linked server
in following way
EXEC sp_addlinkedserver
@.server = 'CostingServer',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'ACCESS',
@.datasrc = 'C:\COSTING2.mdb'
go
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, 'ansari'
go
dbcc traceon(7300,3604)
go
select * from costingServer...materialgroup
here is the error message after runing dbcc traceon(7300,3604)
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Not a valid account name or password.
]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
and if I unset the database password and run the
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, null
it work fine..
And in last the error i have posted i.e
"Invalid schema or catalog specified for provider 'MSDASQL'."
is due to not supplying catalog name. if i change my query
SELECT * FROM COSTINGSERVER...MaterialGroup
to
SELECT * FROM COSTINGSERVER.[c:\costing]..MaterialGroup
it also work fines
ansari
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:m$2w#s$YEHA.3316@.cpmsftngxa06.phx.gbl...
> Could you please run the following-
> dbcc traceon(7300,3604)
> and then execute a query. You'll get a more detailed error message.Please
> paste it here for us to review.
> Cheers,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
AnsariCould you please run the following-
dbcc traceon(7300,3604)
and then execute a query. You'll get a more detailed error message.Please
paste it here for us to review.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||hi Vikram
Thanks for your suggestion. I am sorry to say that I have written down some
incorrect information. I was using Microsoft.JET.OLEDB provider instead
Microsoft OLEDB Provider for ODBC Drivers. I have created the linked server
in following way
EXEC sp_addlinkedserver
@.server = 'CostingServer',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'ACCESS',
@.datasrc = 'C:\COSTING2.mdb'
go
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, 'ansari'
go
dbcc traceon(7300,3604)
go
select * from costingServer...materialgroup
here is the error message after runing dbcc traceon(7300,3604)
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Not a valid account name or password.
]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
and if I unset the database password and run the
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, null
it work fine..
And in last the error i have posted i.e
"Invalid schema or catalog specified for provider 'MSDASQL'."
is due to not supplying catalog name. if i change my query
SELECT * FROM COSTINGSERVER...MaterialGroup
to
SELECT * FROM COSTINGSERVER.[c:\costing]..MaterialGroup
it also work fines
ansari
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:m$2w#s$YEHA.3316@.cpmsftngxa06.phx.gbl...
> Could you please run the following-
> dbcc traceon(7300,3604)
> and then execute a query. You'll get a more detailed error message.Please
> paste it here for us to review.
> Cheers,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
Linked Server (Secure Access Database) Problem
hi all;
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
AnsariCould you please run the following-
dbcc traceon(7300,3604)
and then execute a query. You'll get a more detailed error message.Please
paste it here for us to review.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||hi Vikram
Thanks for your suggestion. I am sorry to say that I have written down some
incorrect information. I was using Microsoft.JET.OLEDB provider instead
Microsoft OLEDB Provider for ODBC Drivers. I have created the linked server
in following way
EXEC sp_addlinkedserver
@.server = 'CostingServer',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'ACCESS',
@.datasrc = 'C:\COSTING2.mdb'
go
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, 'ansari'
go
dbcc traceon(7300,3604)
go
select * from costingServer...materialgroup
here is the error message after runing dbcc traceon(7300,3604)
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Not a valid account name or password.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
and if I unset the database password and run the
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, null
it work fine..
And in last the error i have posted i.e
"Invalid schema or catalog specified for provider 'MSDASQL'."
is due to not supplying catalog name. if i change my query
SELECT * FROM COSTINGSERVER...MaterialGroup
to
SELECT * FROM COSTINGSERVER.[c:\costing]..MaterialGroup
it also work fines
ansari
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:m$2w#s$YEHA.3316@.cpmsftngxa06.phx.gbl...
> Could you please run the following-
> dbcc traceon(7300,3604)
> and then execute a query. You'll get a more detailed error message.Please
> paste it here for us to review.
> Cheers,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
AnsariCould you please run the following-
dbcc traceon(7300,3604)
and then execute a query. You'll get a more detailed error message.Please
paste it here for us to review.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||hi Vikram
Thanks for your suggestion. I am sorry to say that I have written down some
incorrect information. I was using Microsoft.JET.OLEDB provider instead
Microsoft OLEDB Provider for ODBC Drivers. I have created the linked server
in following way
EXEC sp_addlinkedserver
@.server = 'CostingServer',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'ACCESS',
@.datasrc = 'C:\COSTING2.mdb'
go
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, 'ansari'
go
dbcc traceon(7300,3604)
go
select * from costingServer...materialgroup
here is the error message after runing dbcc traceon(7300,3604)
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Not a valid account name or password.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
and if I unset the database password and run the
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, null
it work fine..
And in last the error i have posted i.e
"Invalid schema or catalog specified for provider 'MSDASQL'."
is due to not supplying catalog name. if i change my query
SELECT * FROM COSTINGSERVER...MaterialGroup
to
SELECT * FROM COSTINGSERVER.[c:\costing]..MaterialGroup
it also work fines
ansari
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:m$2w#s$YEHA.3316@.cpmsftngxa06.phx.gbl...
> Could you please run the following-
> dbcc traceon(7300,3604)
> and then execute a query. You'll get a more detailed error message.Please
> paste it here for us to review.
> Cheers,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
Subscribe to:
Posts (Atom)