Can't query linked Access database specified by UNC

Hello,
I link Access database to sql server 2000 with ole db 4 provider; tell it to
logon with username ADMIN, empty password.
If the database is on a local drive, everything works correctly, I see the
tables of the access database in EM, and I successfully run query like
select * from accessdb...table1
If the database is on a remote drive and is specified by UNC, I still see
the tables in EM when EM runs on the server; but when I run EM on another
machine, focusing the tables under the linked server returns error
0x80004005. Same when I try to run a query (even on the server):
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\server\share\accessdb.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Sql server is running on domain administrative account. At the same time as
the message is issued, I can log on that account on the server, launch
Access, and successfully open that same remote database using the same UNC
path.
What gives?
thanks,
Vadim Rapp
Polyscience, Inc.The first is to do is ensure that the database is not being
opened exclusively by other users. If that's all fine then
the error is likely related to permissions.
The account accessing the database needs a minimum of
something similar to Create, Destroy, Read, Write (depending
on the OS) on the folder where the mdb and ldb lives so it
can work with the .ldb file. Those are the minimum
permissions needed in general but it depends upon what
version of Access, what version of MDAC, what OS.
If the permissions are correct, you need to drill down
further to the access control and make sure that the account
has change permissions on the folder where the ldb file
resides. Make sure these are correct by explicitly setting
the permissions for the account.
I've seen several places just use a specific folder for just
the mdb (and consequently the ldb) and give the account full
control over that folder to make things less convoluted in
terms of permissions and the requirement for the ldb file.
-Sue
On Mon, 17 Jan 2005 13:35:38 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:
>Hello,
>I link Access database to sql server 2000 with ole db 4 provider; tell it to
>logon with username ADMIN, empty password.
>If the database is on a local drive, everything works correctly, I see the
>tables of the access database in EM, and I successfully run query like
>select * from accessdb...table1
>If the database is on a remote drive and is specified by UNC, I still see
>the tables in EM when EM runs on the server; but when I run EM on another
>machine, focusing the tables under the linked server returns error
>0x80004005. Same when I try to run a query (even on the server):
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>open the file '\\server\share\accessdb.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>Sql server is running on domain administrative account. At the same time as
>the message is issued, I can log on that account on the server, launch
>Access, and successfully open that same remote database using the same UNC
>path.
>What gives?
>
>thanks,
>Vadim Rapp
>Polyscience, Inc.|||Hello Sue:
You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
SH> The first is to do is ensure that the database is not being
SH> opened exclusively by other users. If that's all fine then
SH> the error is likely related to permissions.
I thought that as well; that's why, as I wrote, I manually logged on the sql
server machine, with the same userid as sqlserver; started Access and opened
the same database successfully.
In other words, I manually did exactly what sql server couldn't do, with the
same login id on the same machine.
Vadim|||Yes...but you still need to check the permissions - the
whole ldb thing. And you need to make sure the permissions
are explicitly set. Logging on and starting access and
opening the database manually is not the same thing.
-Sue
On Tue, 18 Jan 2005 16:07:49 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:
>Hello Sue:
>You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
> SH> The first is to do is ensure that the database is not being
> SH> opened exclusively by other users. If that's all fine then
> SH> the error is likely related to permissions.
>I thought that as well; that's why, as I wrote, I manually logged on the sql
>server machine, with the same userid as sqlserver; started Access and opened
>the same database successfully.
>In other words, I manually did exactly what sql server couldn't do, with the
>same login id on the same machine.
>Vadim|||Not that it's to much help, but I've seen a similar issue with Excel files.
I never really found a solution other than having the files on the
SQLServer. I've also tried with various accounts that has full admin access
to the server, domain, local machine etc. but to no avail. I'd be happy to
know the solution/reason since it's a bit annoying that I have to put the
files on the SQLServer to make it work.
Regards
Steen
Vadim Rapp wrote:
> Hello Sue:
> You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
>> The first is to do is ensure that the database is not being
>> opened exclusively by other users. If that's all fine then
>> the error is likely related to permissions.
> I thought that as well; that's why, as I wrote, I manually logged on
> the sql server machine, with the same userid as sqlserver; started
> Access and opened the same database successfully.
> In other words, I manually did exactly what sql server couldn't do,
> with the same login id on the same machine.
> Vadim|||Hello Steen:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
Fri, 21 Jan 2005 13:02:07 +0100:
SP> Not that it's to much help, but I've seen a similar issue with Excel
SP> files. I never really found a solution other than having the files on
SP> the SQLServer. I've also tried with various accounts that has full
SP> admin access to the server, domain, local machine etc. but to no avail.
SP> I'd be happy to know the solution/reason since it's a bit annoying that
SP> I have to put the files on the SQLServer to make it work.
the only good news is that, as I've found, it works correctly with SQL
Server 2005.
Vadim|||Hello Sue:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
Tue, 18 Jan 2005 16:11:38 -0700:
SH> Yes...but you still need to check the permissions - the
SH> whole ldb thing. And you need to make sure the permissions
SH> are explicitly set. Logging on and starting access and
SH> opening the database manually is not the same thing.
hmm... if I open the database manually, obviously, Access is creating the
ldb... thus, it can do it... How is it not the same thing?
And it looks like in sql server 2005 it works just fine.
Vadim|||SQL Server does not start up Access and then open up an mdb.
It accesses the mdb.
Just like it's different if you double click a file vs. open
an application and then do a File, Open to open the file.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:
>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim|||Vadim,
That last example wasn't a good one. Nonetheless, SQL Server
doesn't start Access and then open a database.
Other than security issues, combinations of MDAC/Jet drivers
can also cause problems and you may want to check those. If
you feel certain that this is a bug in SQL Server, you can
open a case with product support. You will not be charged
for the incident if it is a bug.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:
>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim|||one thing that worked for me on loading dumps from UNCs is that i had
to use an absolute UNC path and not the share name.
for example:
if i had a share named "share" on folder X on my C drive
rather than going to //myComputer/share
i had to go to //myComputer/C$/X
and everything worked fine...it is worth a shot.
hth,
hans
Sue Hoegemeier wrote:
> Vadim,
> That last example wasn't a good one. Nonetheless, SQL Server
> doesn't start Access and then open a database.
> Other than security issues, combinations of MDAC/Jet drivers
> can also cause problems and you may want to check those. If
> you feel certain that this is a bug in SQL Server, you can
> open a case with product support. You will not be charged
> for the incident if it is a bug.
> -Sue
> On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
> <vr@.myrealbox.nospam.com> wrote:
> >Hello Sue:
> >You wrote in conference
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server
on
> >Tue, 18 Jan 2005 16:11:38 -0700:
> >
> > SH> Yes...but you still need to check the permissions - the
> > SH> whole ldb thing. And you need to make sure the permissions
> > SH> are explicitly set. Logging on and starting access and
> > SH> opening the database manually is not the same thing.
> >
> >hmm... if I open the database manually, obviously, Access is
creating the
> >ldb... thus, it can do it... How is it not the same thing?
> >
> >And it looks like in sql server 2005 it works just fine.
> >
> >Vadim

Can't query linked Access database specified by UNC

Hello,
I link Access database to sql server 2000 with ole db 4 provider; tell it to
logon with username ADMIN, empty password.
If the database is on a local drive, everything works correctly, I see the
tables of the access database in EM, and I successfully run query like
select * from accessdb...table1
If the database is on a remote drive and is specified by UNC, I still see
the tables in EM when EM runs on the server; but when I run EM on another
machine, focusing the tables under the linked server returns error
0x80004005. Same when I try to run a query (even on the server):
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\server\share\accessdb.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Sql server is running on domain administrative account. At the same time as
the message is issued, I can log on that account on the server, launch
Access, and successfully open that same remote database using the same UNC
path.
What gives?
thanks,
Vadim Rapp
Polyscience, Inc.
The first is to do is ensure that the database is not being
opened exclusively by other users. If that's all fine then
the error is likely related to permissions.
The account accessing the database needs a minimum of
something similar to Create, Destroy, Read, Write (depending
on the OS) on the folder where the mdb and ldb lives so it
can work with the .ldb file. Those are the minimum
permissions needed in general but it depends upon what
version of Access, what version of MDAC, what OS.
If the permissions are correct, you need to drill down
further to the access control and make sure that the account
has change permissions on the folder where the ldb file
resides. Make sure these are correct by explicitly setting
the permissions for the account.
I've seen several places just use a specific folder for just
the mdb (and consequently the ldb) and give the account full
control over that folder to make things less convoluted in
terms of permissions and the requirement for the ldb file.
-Sue
On Mon, 17 Jan 2005 13:35:38 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:

>Hello,
>I link Access database to sql server 2000 with ole db 4 provider; tell it to
>logon with username ADMIN, empty password.
>If the database is on a local drive, everything works correctly, I see the
>tables of the access database in EM, and I successfully run query like
>select * from accessdb...table1
>If the database is on a remote drive and is specified by UNC, I still see
>the tables in EM when EM runs on the server; but when I run EM on another
>machine, focusing the tables under the linked server returns error
>0x80004005. Same when I try to run a query (even on the server):
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>open the file '\\server\share\accessdb.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>Sql server is running on domain administrative account. At the same time as
>the message is issued, I can log on that account on the server, launch
>Access, and successfully open that same remote database using the same UNC
>path.
>What gives?
>
>thanks,
>Vadim Rapp
>Polyscience, Inc.
|||Hello Sue:
You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
SH> The first is to do is ensure that the database is not being
SH> opened exclusively by other users. If that's all fine then
SH> the error is likely related to permissions.
I thought that as well; that's why, as I wrote, I manually logged on the sql
server machine, with the same userid as sqlserver; started Access and opened
the same database successfully.
In other words, I manually did exactly what sql server couldn't do, with the
same login id on the same machine.
Vadim
|||Yes...but you still need to check the permissions - the
whole ldb thing. And you need to make sure the permissions
are explicitly set. Logging on and starting access and
opening the database manually is not the same thing.
-Sue
On Tue, 18 Jan 2005 16:07:49 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:

>Hello Sue:
>You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
> SH> The first is to do is ensure that the database is not being
> SH> opened exclusively by other users. If that's all fine then
> SH> the error is likely related to permissions.
>I thought that as well; that's why, as I wrote, I manually logged on the sql
>server machine, with the same userid as sqlserver; started Access and opened
>the same database successfully.
>In other words, I manually did exactly what sql server couldn't do, with the
>same login id on the same machine.
>Vadim
|||Not that it's to much help, but I've seen a similar issue with Excel files.
I never really found a solution other than having the files on the
SQLServer. I've also tried with various accounts that has full admin access
to the server, domain, local machine etc. but to no avail. I'd be happy to
know the solution/reason since it's a bit annoying that I have to put the
files on the SQLServer to make it work.
Regards
Steen
Vadim Rapp wrote:
> Hello Sue:
> You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
>
> I thought that as well; that's why, as I wrote, I manually logged on
> the sql server machine, with the same userid as sqlserver; started
> Access and opened the same database successfully.
> In other words, I manually did exactly what sql server couldn't do,
> with the same login id on the same machine.
> Vadim
|||Hello Steen:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.publ ic.sqlserver.server on
Fri, 21 Jan 2005 13:02:07 +0100:
SP> Not that it's to much help, but I've seen a similar issue with Excel
SP> files. I never really found a solution other than having the files on
SP> the SQLServer. I've also tried with various accounts that has full
SP> admin access to the server, domain, local machine etc. but to no avail.
SP> I'd be happy to know the solution/reason since it's a bit annoying that
SP> I have to put the files on the SQLServer to make it work.
the only good news is that, as I've found, it works correctly with SQL
Server 2005.
Vadim
|||Hello Sue:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.publ ic.sqlserver.server on
Tue, 18 Jan 2005 16:11:38 -0700:
SH> Yes...but you still need to check the permissions - the
SH> whole ldb thing. And you need to make sure the permissions
SH> are explicitly set. Logging on and starting access and
SH> opening the database manually is not the same thing.
hmm... if I open the database manually, obviously, Access is creating the
ldb... thus, it can do it... How is it not the same thing?
And it looks like in sql server 2005 it works just fine.
Vadim
|||SQL Server does not start up Access and then open up an mdb.
It accesses the mdb.
Just like it's different if you double click a file vs. open
an application and then do a File, Open to open the file.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:

>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.pub lic.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim
|||Vadim,
That last example wasn't a good one. Nonetheless, SQL Server
doesn't start Access and then open a database.
Other than security issues, combinations of MDAC/Jet drivers
can also cause problems and you may want to check those. If
you feel certain that this is a bug in SQL Server, you can
open a case with product support. You will not be charged
for the incident if it is a bug.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:

>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.pub lic.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim
|||one thing that worked for me on loading dumps from UNCs is that i had
to use an absolute UNC path and not the share name.
for example:
if i had a share named "share" on folder X on my C drive
rather than going to //myComputer/share
i had to go to //myComputer/C$/X
and everything worked fine...it is worth a shot.
hth,
hans
Sue Hoegemeier wrote:
> Vadim,
> That last example wasn't a good one. Nonetheless, SQL Server
> doesn't start Access and then open a database.
> Other than security issues, combinations of MDAC/Jet drivers
> can also cause problems and you may want to check those. If
> you feel certain that this is a bug in SQL Server, you can
> open a case with product support. You will not be charged
> for the incident if it is a bug.
> -Sue
> On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
> <vr@.myrealbox.nospam.com> wrote:
>
>microsoft.public.sqlserver.security,microsoft.pub lic.sqlserver.server
on[vbcol=seagreen]
creating the[vbcol=seagreen]

Can't query linked Access database specified by UNC

Hello,
I link Access database to sql server 2000 with ole db 4 provider; tell it to
logon with username ADMIN, empty password.
If the database is on a local drive, everything works correctly, I see the
tables of the access database in EM, and I successfully run query like
select * from accessdb...table1
If the database is on a remote drive and is specified by UNC, I still see
the tables in EM when EM runs on the server; but when I run EM on another
machine, focusing the tables under the linked server returns error
0x80004005. Same when I try to run a query (even on the server):
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\server\share\accessdb.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Sql server is running on domain administrative account. At the same time as
the message is issued, I can log on that account on the server, launch
Access, and successfully open that same remote database using the same UNC
path.
What gives?
thanks,
Vadim Rapp
Polyscience, Inc.The first is to do is ensure that the database is not being
opened exclusively by other users. If that's all fine then
the error is likely related to permissions.
The account accessing the database needs a minimum of
something similar to Create, Destroy, Read, Write (depending
on the OS) on the folder where the mdb and ldb lives so it
can work with the .ldb file. Those are the minimum
permissions needed in general but it depends upon what
version of Access, what version of MDAC, what OS.
If the permissions are correct, you need to drill down
further to the access control and make sure that the account
has change permissions on the folder where the ldb file
resides. Make sure these are correct by explicitly setting
the permissions for the account.
I've seen several places just use a specific folder for just
the mdb (and consequently the ldb) and give the account full
control over that folder to make things less convoluted in
terms of permissions and the requirement for the ldb file.
-Sue
On Mon, 17 Jan 2005 13:35:38 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:

>Hello,
>I link Access database to sql server 2000 with ole db 4 provider; tell it t
o
>logon with username ADMIN, empty password.
>If the database is on a local drive, everything works correctly, I see the
>tables of the access database in EM, and I successfully run query like
>select * from accessdb...table1
>If the database is on a remote drive and is specified by UNC, I still see
>the tables in EM when EM runs on the server; but when I run EM on another
>machine, focusing the tables under the linked server returns error
>0x80004005. Same when I try to run a query (even on the server):
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine ca
nnot
>open the file '\\server\share\accessdb.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>Sql server is running on domain administrative account. At the same time as
>the message is issued, I can log on that account on the server, launch
>Access, and successfully open that same remote database using the same UNC
>path.
>What gives?
>
>thanks,
>Vadim Rapp
>Polyscience, Inc.|||Hello Sue:
You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
SH> The first is to do is ensure that the database is not being
SH> opened exclusively by other users. If that's all fine then
SH> the error is likely related to permissions.
I thought that as well; that's why, as I wrote, I manually logged on the sql
server machine, with the same userid as sqlserver; started Access and opened
the same database successfully.
In other words, I manually did exactly what sql server couldn't do, with the
same login id on the same machine.
Vadim|||Yes...but you still need to check the permissions - the
whole ldb thing. And you need to make sure the permissions
are explicitly set. Logging on and starting access and
opening the database manually is not the same thing.
-Sue
On Tue, 18 Jan 2005 16:07:49 -0600, "Vadim Rapp"
<vrapp@.nospam.polyscience.com> wrote:

>Hello Sue:
>You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
> SH> The first is to do is ensure that the database is not being
> SH> opened exclusively by other users. If that's all fine then
> SH> the error is likely related to permissions.
>I thought that as well; that's why, as I wrote, I manually logged on the sq
l
>server machine, with the same userid as sqlserver; started Access and opene
d
>the same database successfully.
>In other words, I manually did exactly what sql server couldn't do, with th
e
>same login id on the same machine.
>Vadim|||Not that it's to much help, but I've seen a similar issue with Excel files.
I never really found a solution other than having the files on the
SQLServer. I've also tried with various accounts that has full admin access
to the server, domain, local machine etc. but to no avail. I'd be happy to
know the solution/reason since it's a bit annoying that I have to put the
files on the SQLServer to make it work.
Regards
Steen
Vadim Rapp wrote:
> Hello Sue:
> You wrote on Tue, 18 Jan 2005 13:23:38 -0700:
>
> I thought that as well; that's why, as I wrote, I manually logged on
> the sql server machine, with the same userid as sqlserver; started
> Access and opened the same database successfully.
> In other words, I manually did exactly what sql server couldn't do,
> with the same login id on the same machine.
> Vadim|||Hello Steen:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
Fri, 21 Jan 2005 13:02:07 +0100:
SP> Not that it's to much help, but I've seen a similar issue with Excel
SP> files. I never really found a solution other than having the files on
SP> the SQLServer. I've also tried with various accounts that has full
SP> admin access to the server, domain, local machine etc. but to no avail.
SP> I'd be happy to know the solution/reason since it's a bit annoying that
SP> I have to put the files on the SQLServer to make it work.
the only good news is that, as I've found, it works correctly with SQL
Server 2005.
Vadim|||Hello Sue:
You wrote in conference
microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
Tue, 18 Jan 2005 16:11:38 -0700:
SH> Yes...but you still need to check the permissions - the
SH> whole ldb thing. And you need to make sure the permissions
SH> are explicitly set. Logging on and starting access and
SH> opening the database manually is not the same thing.
hmm... if I open the database manually, obviously, Access is creating the
ldb... thus, it can do it... How is it not the same thing?
And it looks like in sql server 2005 it works just fine.
Vadim|||SQL Server does not start up Access and then open up an mdb.
It accesses the mdb.
Just like it's different if you double click a file vs. open
an application and then do a File, Open to open the file.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:

>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim|||Vadim,
That last example wasn't a good one. Nonetheless, SQL Server
doesn't start Access and then open a database.
Other than security issues, combinations of MDAC/Jet drivers
can also cause problems and you may want to check those. If
you feel certain that this is a bug in SQL Server, you can
open a case with product support. You will not be charged
for the incident if it is a bug.
-Sue
On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
<vr@.myrealbox.nospam.com> wrote:

>Hello Sue:
>You wrote in conference
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server on
>Tue, 18 Jan 2005 16:11:38 -0700:
> SH> Yes...but you still need to check the permissions - the
> SH> whole ldb thing. And you need to make sure the permissions
> SH> are explicitly set. Logging on and starting access and
> SH> opening the database manually is not the same thing.
>hmm... if I open the database manually, obviously, Access is creating the
>ldb... thus, it can do it... How is it not the same thing?
>And it looks like in sql server 2005 it works just fine.
>Vadim|||one thing that worked for me on loading dumps from UNCs is that i had
to use an absolute UNC path and not the share name.
for example:
if i had a share named "share" on folder X on my C drive
rather than going to //myComputer/share
i had to go to //myComputer/C$/X
and everything worked fine...it is worth a shot.
hth,
hans
Sue Hoegemeier wrote:
> Vadim,
> That last example wasn't a good one. Nonetheless, SQL Server
> doesn't start Access and then open a database.
> Other than security issues, combinations of MDAC/Jet drivers
> can also cause problems and you may want to check those. If
> you feel certain that this is a bug in SQL Server, you can
> open a case with product support. You will not be charged
> for the incident if it is a bug.
> -Sue
> On Sat, 22 Jan 2005 23:24:46 -0600, "Vadim Rapp"
> <vr@.myrealbox.nospam.com> wrote:
>
>microsoft.public.sqlserver.security,microsoft.public.sqlserver.server
on[vbcol=seagreen]
creating the[vbcol=seagreen]

Can't Open DTS package - password

I have a SqlSrvr 2k system; a year ago I created a dts
package owned by administrator. I need to open it to
create a copy to move to another system. When I try
to open the package it prompts me for a password; I enter
the administrator's password - which fails. I am logged
in as administrator. How can I open this package ? There
are no security errors in the Sql Server log or the
system errorlog.
Thanks,
John Yard
UCLAThere is not a way to retrieve a password from a DTS package. There is no
way to reset it or change it either. The only way to change the password is
to save the package as a different name and the only way to do this is if
you can open it in the DTS Designer.
HTH
Ryan Waight, MCDBA, MCSE
"John Yard" <anonymous@.discussions.microsoft.com> wrote in message
news:05e101c3a33b$22aac240$a401280a@.phx.gbl...
> I have a SqlSrvr 2k system; a year ago I created a dts
> package owned by administrator. I need to open it to
> create a copy to move to another system. When I try
> to open the package it prompts me for a password; I enter
> the administrator's password - which fails. I am logged
> in as administrator. How can I open this package ? There
> are no security errors in the Sql Server log or the
> system errorlog.
> Thanks,
> John Yard
> UCLA

Can't login with windows user

Why I can't login to a SQL database with windows username and password (both
in OLE DB and ODBC).
If I try with trusted connection (or windows authentication) with the same
user works, but if I write by hand user name (domain\user) and password I
get "Error in initilizing provider". With SQL users works.
Hi,
For trusted connection you are not supposed to specify the user name and
password.
A single login to Operating system will give you access to SQL Server as
well. All you need is to click the "Windows Authentication" and click OK.
This will allow you to access the SQl Server with the credentials of
currently logged in OS user.
User name and password needs to be provided only for SQL server based
logins.
Thanks
Hari
MCDBA
"Sorin Sandu" <ssandu@.bizland.ro.invalid> wrote in message
news:O8x4qMSSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Why I can't login to a SQL database with windows username and password
(both
> in OLE DB and ODBC).
> If I try with trusted connection (or windows authentication) with the same
> user works, but if I write by hand user name (domain\user) and password I
> get "Error in initilizing provider". With SQL users works.
>
|||But I don't want to access the SQl Server with the credentials of
currently logged in OS user. I need another windows user. Windows not SQL
|||Hi,
We cant enter the user name for the windows based user. A single login to OS
will allow allow to login to sql server.
But you could try this:-
1. Map the network (network mapping) to the SQL server using the Windows
user you need to connect to sql server (This user should have rights in sql
server)
2. Now using query analyzer try to connect.
This uses the mapped users security context to login inside sql server
Thanks
Hari
MCDBA
"Sorin Sandu" <ssandu@.bizland.ro.invalid> wrote in message
news:OZcYjreSEHA.644@.tk2msftngp13.phx.gbl...
> But I don't want to access the SQl Server with the credentials of
> currently logged in OS user. I need another windows user. Windows not SQL
>
|||Hi
Please what do you mean by map the network (network mapping) ?
|||Mapping a network drive is to create a namesake, say "J" drive which is
actually pointing to a share across your network, like \\shared1\sqlfiles.
To do so, open Windows explorer, go to the Tools menu, you'll find "Map
network drive" as the 1st option. That option gives you the drive 'letter'
you want to choose, as well as the "folder" which is the network share. You
will of course have to be using a domain account, and that should have
necessary permissions to access that shared folder.
Hope that helps!
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.

Can't login with windows user

Why I can't login to a SQL database with windows username and password (both
in OLE DB and ODBC).
If I try with trusted connection (or windows authentication) with the same
user works, but if I write by hand user name (domain\user) and password I
get "Error in initilizing provider". With SQL users works.Hi,
For trusted connection you are not supposed to specify the user name and
password.
A single login to Operating system will give you access to SQL Server as
well. All you need is to click the "Windows Authentication" and click OK.
This will allow you to access the SQl Server with the credentials of
currently logged in OS user.
User name and password needs to be provided only for SQL server based
logins.
Thanks
Hari
MCDBA
"Sorin Sandu" <ssandu@.bizland.ro.invalid> wrote in message
news:O8x4qMSSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Why I can't login to a SQL database with windows username and password
(both
> in OLE DB and ODBC).
> If I try with trusted connection (or windows authentication) with the same
> user works, but if I write by hand user name (domain\user) and password I
> get "Error in initilizing provider". With SQL users works.
>|||But I don't want to access the SQl Server with the credentials of
currently logged in OS user. I need another windows user. Windows not SQL|||Hi,
We cant enter the user name for the windows based user. A single login to OS
will allow allow to login to sql server.
But you could try this:-
1. Map the network (network mapping) to the SQL server using the Windows
user you need to connect to sql server (This user should have rights in sql
server)
2. Now using query analyzer try to connect.
This uses the mapped users security context to login inside sql server
Thanks
Hari
MCDBA
"Sorin Sandu" <ssandu@.bizland.ro.invalid> wrote in message
news:OZcYjreSEHA.644@.tk2msftngp13.phx.gbl...
> But I don't want to access the SQl Server with the credentials of
> currently logged in OS user. I need another windows user. Windows not SQL
>|||Hi
Please what do you mean by map the network (network mapping) ?|||Mapping a network drive is to create a namesake, say "J" drive which is
actually pointing to a share across your network, like \\shared1\sqlfiles.
To do so, open Windows explorer, go to the Tools menu, you'll find "Map
network drive" as the 1st option. That option gives you the drive 'letter'
you want to choose, as well as the "folder" which is the network share. You
will of course have to be using a domain account, and that should have
necessary permissions to access that shared folder.
Hope that helps!
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.

Can't login with windows user

Why I can't login to a SQL database with windows username and password (both
in OLE DB and ODBC).
If I try with trusted connection (or windows authentication) with the same
user works, but if I write by hand user name (domain\user) and password I
get "Error in initilizing provider". With SQL users works.Hi,
For trusted connection you are not supposed to specify the user name and
password.
A single login to Operating system will give you access to SQL Server as
well. All you need is to click the "Windows Authentication" and click OK.
This will allow you to access the SQl Server with the credentials of
currently logged in OS user.
User name and password needs to be provided only for SQL server based
logins.
Thanks
Hari
MCDBA
"Sorin Sandu" <ssandu@.bizland.ro.invalid> wrote in message
news:O8x4qMSSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Why I can't login to a SQL database with windows username and password
(both
> in OLE DB and ODBC).
> If I try with trusted connection (or windows authentication) with the same
> user works, but if I write by hand user name (domain\user) and password I
> get "Error in initilizing provider". With SQL users works.
>

cant login to SQL Server anymore

Hi guys-
I can't seem to be able to login into SQL Server anymore ever since I c hanged my password for my Win 2k login. the authentication mode (i checked in the properties section ) is set to SQL Server authentication, but i know originally i set it up as Win 2k authentication, and then changed that to SQL Server authentication...the local server (its set up on my machine) will not start up any more, and I am clueless as to how to fix it. I've tried to login using my other ids ( that i've created) but i don't think those id's had sa rights, so it won't let me.

Please help!Hi Indigo4267,
Try the checking the following:
1). Using the 'Client Network Utility' in the MS Menu, click on the
'Alias' tab.

2). If you don't see your DB server in this list, add it as a Named Pipes
alias.

You should be able to logon direct now, since the DB is on your
local workstation.

3). Check to see what domain/user privileges you have as a user in
the domain that is running the MSSqlServer process. You may not
be able to 'see' the domain running the server.

Good luck!|||thanks for the advice. i'm really new to this db admin stuff (and sql server in general) so i only understood part 1 of what you wrote, and i followed your steps, but it still doesn't work...<sigh>

i guess the real underlying problem is "is there a way to change your win 2k password (if you are the db admin) and still be able to login into sql server (if its set up on your machine) regardless of what authentication mode is set up?" even when i switched to win authentication from sql authentication/mixed mode authentication it still failed!

:( i always get weird problems like this in everything i touch :(|||I actually managed to solve this problem...i think my SQL Server was set to mixed mode which is why i was having problems with my new win2k password--

here's how i did it (for others who are facing teh same problem)
in win2k-
-go to the control panel
-click on administrative tools
-click on services
-right click on MSSQLServer and choose properties
-click on logon tab
-enter and confirm new password
-restart SQL Server

its actually in the help section (for SQL Server 2k) under the heading "Changing Passwords and User Accounts" and there also is info there for win 4.0 NT.

cant load rep mgr without user + pass prompt ?

When loading Report Manager on the hosting Rep Service machine i cant access
any options without a password + user. I cant get any to work.
When setting up Rep Services in every option possible i defiend a local
service account at first. I then uninstalled the software and installed
again users the domain admin account.
Any idea why i cant login to the manager options ?
Thanks
Scotti needed to login as localhost/administrator.....there is hope yet.

Cant install msde on server2000

Anybody out there know why i can't install MSDE on a win2000 server. I start
setup in dos mode with sapwd ="password" but it dies without error
messages.
Thanks in advance
hi,
news wrote:
> Anybody out there know why i can't install MSDE on a win2000 server.
> I start setup in dos mode with sapwd ="password" but it dies without
> error messages.
> Thanks in advance
please add verbose logging of the installation process providing the
/L*v "c:\msde.log"
parameter to your call to setup.exe boostrap installer... this will output
an about 2mb text file (for a succesfull installation) you can later inspect
for
RETURN VALUE 3
entries... about 10/15 lines before each entry som (sometime cryptic)
description of the problem will be reported..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi thanks for the reply i tried it nut cant read the cryptic merror report
or rather cant understand it
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3ii0fpFljhkoU1@.individual.net...
> hi,
> news wrote:
> please add verbose logging of the installation process providing the
> /L*v "c:\msde.log"
> parameter to your call to setup.exe boostrap installer... this will output
> an about 2mb text file (for a succesfull installation) you can later
> inspect for
> RETURN VALUE 3
> entries... about 10/15 lines before each entry som (sometime cryptic)
> description of the problem will be reported..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||him
news wrote:
> Hi thanks for the reply i tried it nut cant read the cryptic merror
> report or rather cant understand it
perhaps we can help you...
try reporting the lines indicating the problem..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||See my post "Corrupted MSDE files encountered" posted 7/2/2005. Maybe you
have the same problem I did.
Axel Larson,
Share What You Know,
Learn What You Don't
"news" <news@.c.com> wrote in message
news:HL-dnaakovg48F7fRVn-ow@.rogers.com...
> Anybody out there know why i can't install MSDE on a win2000 server. I
start
> setup in dos mode with sapwd ="password" but it dies without error
> messages.
> Thanks in advance
>
|||Hi, I don't know if I have the same problem as News.
This is just the last part of the log file.
Thanks!
MSI (c) (58:60) [09:49:42:814]: Note: 1: 1708
MSI (c) (58:60) [09:49:42:814]: Product: Microsoft SQL Server Desktop
Engine -- Installation operation failed.
MSI (c) (58:60) [09:49:42:830]: Grabbed execution mutex.
MSI (c) (58:60) [09:49:42:830]: Cleaning up uninstalled install packages, if
any exist
MSI (c) (58:60) [09:49:42:830]: MainEngineThread is returning 1603
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3ij10tFlrda6U1@.individual.net...
> him
> news wrote:
> perhaps we can help you...
> try reporting the lines indicating the problem..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
Gule wrote:
> Hi, I don't know if I have the same problem as News.
> This is just the last part of the log file.
please isolate the relevand part of the log reporting
RETURN VALUE 3
... about 10/15 lines before each entry some (sometime cryptic) description
of the problem will be reported..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Actually, i solved the problem. I run the setup2.msi and installation passed
without problem!?
Thanks anyway!
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3jnfloFqq68uU1@.individual.net...
> hi,
> Gule wrote:
> please isolate the relevand part of the log reporting
> RETURN VALUE 3
> ... about 10/15 lines before each entry some (sometime cryptic)
> description of the problem will be reported..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Hi Andrea,
We are experiencing the same issue while installing MSDE 2K on a Windows 2K
(SP3) machine. It gives out the error: 1603.
Here is the excerpt from the MSDE install verbose log of the 10-15 lines
before the 'Return Value 3' happens (2 occurences in the log)
-----
-----
1.Starting custom action SkuIt
MSI (s) (24:C0): Executing op:
ActionStart(Name=ConfigServer.2D02443E_7002_4C0B_A BC9_EAB2C064397B...
Action 14:03:31: ConfigServer.2D02443E_7002_4C0B_ABC9_EAB2C064397B. ..
MSI (s) (24:C0): Executing op:
CustomActionSchedule(Action=ConfigServer.2D02443E_ 7002_4C0B_ABC9_EAB2C064397B...
MSI (s) (24:C0): Creating MSIHANDLE (214) of type 790536 for thread 1984
SOFTWARE\Microsoft\Microsoft SQL Server
Setup.{E09B48B5-E141-427A-AB0C-D3605127224A}
TempFolder is
C:\DOCUME~1\D35166.000\LOCALS~1\Temp\{E09B48B5-E141-427A-AB0C-D3605127224A}\
Loading extended custom action library
C:\DOCUME~1\D35166.000\LOCALS~1\Temp\{E09B48B5-E141-427A-AB0C-D3605127224A}\sqlcax.dll
SOFTWARE\Microsoft\Microsoft SQL Server
Setup.{E09B48B5-E141-427A-AB0C-D3605127224A}
Starting custom action ConfigServer
Executing "C:\WINNT\Program Files\Microsoft SQL
Server\80\Tools\Binn\cnfgsvr.exe -V 1 -M 1 -U sa -I "MSSQLSERVER" -Q
"SQL_Latin1_General_CP1_CI_AS""
Setup failed to configure the server. Refer to the server error logs and
setup error logs for more information.
Action ended 14:04:04: InstallFinalize. Return value 3.
-----
2. Start custom action DeferProperties
Defer Properties returns: 0
MSI (s) (24:C0): Executing op: End(Checksum=0,ProgressTotal=0)
MSI (s) (24:C0): Error in rollback skipped. Return: 5
MSI (s) (24:C0): FEATURECACHE: Entering Invalidate
MSI (s) (24:C0): Unlocking Server
Action ended 14:04:24: INSTALL. Return value 3.
-----
-----
I will greatly appreciate any direction to help resolve this issue.
Regards,
Srinivas
"Andrea Montanari" wrote:

> hi,
> Gule wrote:
> please isolate the relevand part of the log reporting
> RETURN VALUE 3
> ... about 10/15 lines before each entry some (sometime cryptic) description
> of the problem will be reported..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi,
Srinivas Rampalli wrote:
> Hi Andrea,
> "SQL_Latin1_General_CP1_CI_AS""
> Setup failed to configure the server. Refer to the server error logs
> and setup error logs for more information.
> Action ended 14:04:04: InstallFinalize. Return value 3.
please have a look at
http://support.microsoft.com/default...99&Product=sql
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Can't Get SQL Authentication to work With XP Pro SP2 in very simple test case

I'm trying to work my way through the steps of using a User Id and Password in a connection string.

I'm working with SQL 2005 Express, VS2005, in the development server. Got an error I can't get around...tried it several diffent ways on a slightly more complicated test site...no joy...so went to the MSDN tutorial...made the most "vanilla" test I could think of, and still can't figure it out.

I thought it would be simple enough that I could post the whole thing (below)

The test works fine with Integrated Security = True in the connection string. When I remove that phrase, I get the error:

{"CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file E:\MyPath\App_Data\VSST_DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."}

This occurs on the cn.Open statement below.

It gets past the login, so I know that the SQL User and password match up correctly.

==========================
<connectionStrings>
<add name="VSST_CN"
connectionString="Data Source=MyServer\SQLEXPRESS;AttachDbFilename=E:\MyPath\App_Data\VSST_DB.mdf;User Id = VSST; Password=vsst123"
providerName="System.Data.SqlClient"/>
</connectionStrings>

=========================
Page Code Behind (no controls on page)

Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("VSST_CN").ToString())

Dim cmd As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM VSST_Table", cn)
cn.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
rdr.Read()
Response.Write(rdr(0).ToString())

End Sub
End Class

=================
The DB

Table VSST_Table,
ID is int, primary key, identity
Field1, Field2, Field3, Field4, Field5 are varchar(50)

I added one record ("A", "B", "C", "D", "E") to the table through VS2005 Server Explorer

This shows up in Count = 1 from running the page when Integrated Security = True

=================
In SSMSE: (this is ALL I did, tried to use the minimum so not to confuse...)

I added the SQL Authentication Server level user "VSST" with the password "vsst123" (and the login works, as noted above)

I attach the .mdf

I add VSST to the Database Users, and give it db_owner

I add VSST to the Table with all permissions checked.

=====================================

I can't figure this out. This is a very vanilla test and I'm stumped. I'm about to give up on SQL Authentication entirely (at least for now), and just try to filter my inputs for SQL Injections...that's the only reason I have (at this stage in my biz plan) for needing SQL Authentication. On the other hand, I really don't like being this stumped on something that is so widely promoted as a common practice.

Any help on this would be greatly appreciated.

Thanks!

It's an XP SP2 security issue. The connect is running within an application sandbox which is not granted the authority to attach, create, or restore a database. The integrated security scenario works, because the Windows credentials are passed through. We spent days trying to figure a way around this one when dealing with a click once application installation and finally gave up. There wasn't a single permutation that we could use to get around the security restrictions that XP SP2 put in place. If the database already existed, everything connected and worked just fine. But, it simply refused to attach a database.|||

Thanks for the input. Wow. Good to know I wasn't completely blowing it....it looked too easy.

So, maybe it will work on the Windows 2003 VPS Server I'm renting...I guess I'll have to give it a try. But maybe it tells me I need to consider why SQL Authentication is critical to this app.

Grazie!

|||

I think there was something in the decision tree that I missed...

As I am gradually putting this together, it looks like the first choice is "1 server => windows authentication" vs "multiple servers (plus some other cases) => sql authentication"

After that, everything that is written about sql authentication seems to assume a level of professional expertise which is appropriately associated with a multi-server environment. Which probably means that the developers may have XP on their desks, but that they're logging into an W2003 development environment...so they never get exposed to this problem (or are aware of it and bypass it).

If you miss the first branch in the decision tree, this is never mentioned...it's all about encrypting web.config and guarding against sql injection by limiting table access, clever ways to use trusted this and that....but never "maybe you should just use Windows Authentication, if it's a single server app."

Long way to the first u-turn....hazards of being a newbit!

|||

Similar grief here

http://www.mcse.ms/message2347651.html

Can't Get SQL Authentication to work in very simple test case

I'm trying to work my way through the steps of using a User Id and Password in a connection string.

I'm working with SQL 2005 Express, VS2005, in the development server. Got an error I can't get around...tried it several diffent ways on a slightly more complicated test site...no joy...so went to the MSDN tutorial...made the most "vanilla" test I could think of, and still can't figure it out.

I thought it would be simple enough that I could post the whole thing (below)

The test works fine with Integrated Security = True in the connection string. When I remove that phrase, I get the error:

{"CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file E:\MyPath\App_Data\VSST_DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."}

This occurs on the cn.Open statement below.

It gets past the login, so I know that the SQL User and password match up correctly.

==========================
<connectionStrings>
<add name="VSST_CN"
connectionString="Data Source=MyServer\SQLEXPRESS;AttachDbFilename=E:\MyPath\App_Data\VSST_DB.mdf;User Id = VSST; Password=vsst123"
providerName="System.Data.SqlClient"/>
</connectionStrings>

=========================
Page Code Behind (no controls on page)

Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("VSST_CN").ToString())

Dim cmd As SqlCommand = New SqlCommand("SELECT COUNT(*) FROM VSST_Table", cn)
cn.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
rdr.Read()
Response.Write(rdr(0).ToString())

End Sub
End Class

=================
The DB

Table VSST_Table,
ID is int, primary key, identity
Field1, Field2, Field3, Field4, Field5 are varchar(50)

I added one record ("A", "B", "C", "D", "E") to the table through VS2005 Server Explorer

This shows up in Count = 1 from running the page when Integrated Security = True

=================
In SSMSE: (this is ALL I did, tried to use the minimum so not to confuse...)

I added the SQL Authentication Server level user "VSST" with the password "vsst123" (and the login works, as noted above)

I attach the .mdf

I add VSST to the Database Users, and give it db_owner

I add VSST to the Table with all permissions checked.

=====================================

I can't figure this out. This is a very vanilla test and I'm stumped. I'm about to give up on SQL Authentication entirely (at least for now), and just try to filter my inputs for SQL Injections...that's the only reason I have (at this stage in my biz plan) for needing SQL Authentication. On the other hand, I really don't like being this stumped on something that is so widely promoted as a common practice.

Any help on this would be greatly appreciated.

Thanks!

It's an XP SP2 security issue. The connect is running within an application sandbox which is not granted the authority to attach, create, or restore a database. The integrated security scenario works, because the Windows credentials are passed through. We spent days trying to figure a way around this one when dealing with a click once application installation and finally gave up. There wasn't a single permutation that we could use to get around the security restrictions that XP SP2 put in place. If the database already existed, everything connected and worked just fine. But, it simply refused to attach a database.|||

Thanks for the input. Wow. Good to know I wasn't completely blowing it....it looked too easy.

So, maybe it will work on the Windows 2003 VPS Server I'm renting...I guess I'll have to give it a try. But maybe it tells me I need to consider why SQL Authentication is critical to this app.

Grazie!

|||

I think there was something in the decision tree that I missed...

As I am gradually putting this together, it looks like the first choice is "1 server => windows authentication" vs "multiple servers (plus some other cases) => sql authentication"

After that, everything that is written about sql authentication seems to assume a level of professional expertise which is appropriately associated with a multi-server environment. Which probably means that the developers may have XP on their desks, but that they're logging into an W2003 development environment...so they never get exposed to this problem (or are aware of it and bypass it).

If you miss the first branch in the decision tree, this is never mentioned...it's all about encrypting web.config and guarding against sql injection by limiting table access, clever ways to use trusted this and that....but never "maybe you should just use Windows Authentication, if it's a single server app."

Long way to the first u-turn....hazards of being a newbit!

|||

Similar grief here

http://www.mcse.ms/message2347651.html