LogReader SubSystem

I'm trying to setup a publisher on an msde database and am getting an error
when running my script. The error in the event viewer (and job history)
is -
Step 2 of job 'machine\instance-database-1' cannot be run because the
LogReader subsystem failed to load. The job has been suspended.
The job category is REPL -LogReader. Any ideas what I can do or what I
missed?
TIA,
Dave
on your msde server open a command prompt and go to c:\program
files\Microsoft sql server\80\com and type logread
do you get an error message?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dave" <dave.golightly@.stgutah.com> wrote in message
news:uZYCJIYpEHA.2304@.TK2MSFTNGP14.phx.gbl...
> I'm trying to setup a publisher on an msde database and am getting an
error
> when running my script. The error in the event viewer (and job history)
> is -
> Step 2 of job 'machine\instance-database-1' cannot be run because the
> LogReader subsystem failed to load. The job has been suspended.
> The job category is REPL -LogReader. Any ideas what I can do or what I
> missed?
> TIA,
> Dave
>
|||I got -
"'logread' is not recognized as an internal or external command, operable
program or batch file"
So the installshield object I'm using to install the msde isn't installing
the logread file. Is there any other files I should be checking for?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uhmT8OZpEHA.744@.TK2MSFTNGP10.phx.gbl...
> on your msde server open a command prompt and go to c:\program
> files\Microsoft sql server\80\com and type logread
> do you get an error message?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Dave" <dave.golightly@.stgutah.com> wrote in message
> news:uZYCJIYpEHA.2304@.TK2MSFTNGP14.phx.gbl...
> error
>
|||Oops, I'm making a huge mistake here. MSDE servers are not enable to be
transactional publishers. You will have to make it a merge publisher.
Sorry about that.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dave" <dave.golightly@.stgutah.com> wrote in message
news:Os127ZZpEHA.3688@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I got -
> "'logread' is not recognized as an internal or external command, operable
> program or batch file"
> So the installshield object I'm using to install the msde isn't installing
> the logread file. Is there any other files I should be checking for?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uhmT8OZpEHA.744@.TK2MSFTNGP10.phx.gbl...
history)
>
|||I'm trying to create a snapshot publisher. Is there a setting I've got
wrong?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eG0FwfZpEHA.592@.TK2MSFTNGP11.phx.gbl...
> Oops, I'm making a huge mistake here. MSDE servers are not enable to be
> transactional publishers. You will have to make it a merge publisher.
> Sorry about that.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Dave" <dave.golightly@.stgutah.com> wrote in message
> news:Os127ZZpEHA.3688@.TK2MSFTNGP09.phx.gbl...
> history)
>
|||Here's the script -
-- Adding the snapshot publication
exec sp_addpublication @.publication = N'inspPro Snapshot Publication',
@.restricted = N'false', @.sync_method = N'native', @.repl_freq = N'snapshot',
@.description = N'Snapshot publication of inspPro database from Publisher
[workstation]\INSPPRO.', @.status = N'active', @.allow_push = N'true',
@.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet =
N'false', @.independent_agent = N'true', @.immediate_sync = N'true',
@.allow_sync_tran = N'false', @.autogen_sync_procs = N'true', @.retention = 0,
@.allow_queued_tran = N'true', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false', @.conflict_policy
= N'sub reinit', @.centralized_conflicts = N'true', @.conflict_retention = 14,
@.queue_type = N'sql', @.add_to_active_directory = N'false',
@.logreader_job_name = N'[workstation]\INSPPRO-inspPro-1'
GO
exec sp_addpublication_snapshot @.publication = N'inspPro Snapshot
Publication',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
@.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.snapshot_job_name = N'inspPro Snapshot
Snapshot Agent'
GO
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eG0FwfZpEHA.592@.TK2MSFTNGP11.phx.gbl...
> Oops, I'm making a huge mistake here. MSDE servers are not enable to be
> transactional publishers. You will have to make it a merge publisher.
> Sorry about that.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Dave" <dave.golightly@.stgutah.com> wrote in message
> news:Os127ZZpEHA.3688@.TK2MSFTNGP09.phx.gbl...
> history)
>
|||This looks like a updateable subscription using the queue reader. I don't
believe this is supported on MSDE where MSDE is the publisher.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dave" <dave.golightly@.stgutah.com> wrote in message
news:e0W6CpZpEHA.3868@.TK2MSFTNGP15.phx.gbl...
> Here's the script -
> -- Adding the snapshot publication
> exec sp_addpublication @.publication = N'inspPro Snapshot Publication',
> @.restricted = N'false', @.sync_method = N'native', @.repl_freq =
N'snapshot',
> @.description = N'Snapshot publication of inspPro database from Publisher
> [workstation]\INSPPRO.', @.status = N'active', @.allow_push = N'true',
> @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet =
> N'false', @.independent_agent = N'true', @.immediate_sync = N'true',
> @.allow_sync_tran = N'false', @.autogen_sync_procs = N'true', @.retention =
0,
> @.allow_queued_tran = N'true', @.snapshot_in_defaultfolder = N'true',
> @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> @.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.conflict_policy
> = N'sub reinit', @.centralized_conflicts = N'true', @.conflict_retention =
14,
> @.queue_type = N'sql', @.add_to_active_directory = N'false',
> @.logreader_job_name = N'[workstation]\INSPPRO-inspPro-1'
> GO
> exec sp_addpublication_snapshot @.publication = N'inspPro Snapshot
> Publication',@.frequency_type = 4, @.frequency_interval = 1,
> @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0,
> @.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date
=[vbcol=seagreen]
> 0, @.active_end_date = 0, @.active_start_time_of_day = 0,
> @.active_end_time_of_day = 235959, @.snapshot_job_name = N'inspPro Snapshot
> Snapshot Agent'
> GO
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eG0FwfZpEHA.592@.TK2MSFTNGP11.phx.gbl...
operable[vbcol=seagreen]
an[vbcol=seagreen]
the[vbcol=seagreen]
what
>

login works with SQL but not MSDE

Any idea why the following dmo connection string would not work on MSDE, but
does work on SQL Personal Edition? It appears to connects, but then can't
find the server name. In both cases we are using the latest SQL Personal,
MSDE, and Windows XP.
Thanks for any guidance.
Neil
connection string:
server=myserver;trusted_connection="yes";database= northwind;connection
timeout=10;
exception:
[INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
timeout=10;
[EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login, Object
Password)
at SQLServer.Connect()
Was MSDE installed with the network protocols enabled? With SP3a they are
turned off by default.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> Any idea why the following dmo connection string would not work on MSDE,
but
> does work on SQL Personal Edition? It appears to connects, but then can't
> find the server name. In both cases we are using the latest SQL Personal,
> MSDE, and Windows XP.
> Thanks for any guidance.
> Neil
> connection string:
> server=myserver;trusted_connection="yes";database= northwind;connection
> timeout=10;
> exception:
> [INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
> timeout=10;
> [EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login,
Object
> Password)
> at SQLServer.Connect()
>
|||This took me a little bit further. I can now actually see the tables in in
my program using the dmo calls. Thanks! owever, when I try to access the
individual columns, I continue to get errors such as:
EXCEPTION] 20040520 08:37:30.955 (sqlserver.myObject) Error retrieving info
from test2: Server user 'mymachine\mylogin' is not a valid user in database
'test2'.
Again, this all works fine using SQL Personal Edition.
Neil
"Jim Young" <thorium48@.hotmail.com> wrote in message
Was MSDE installed with the network protocols enabled? With SP3a they are
turned off by default.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> Any idea why the following dmo connection string would not work on MSDE,
but
> does work on SQL Personal Edition? It appears to connects, but then can't
> find the server name. In both cases we are using the latest SQL Personal,
> MSDE, and Windows XP.
> Thanks for any guidance.
> Neil
> connection string:
> server=myserver;trusted_connection="yes";database= northwind;connection
> timeout=10;
> exception:
> [INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
> timeout=10;
> [EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login,
Object
> Password)
> at SQLServer.Connect()
>
|||Carefully read the error message. You need to make the user you are
connecting with, in this case a Windows login, a user in the database you
are trying to use. Everyone that can connect to a server can see the schema
of any database, but you can't select or change any data until you are given
explicit permissions in a database.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:OShgsvmPEHA.2468@.TK2MSFTNGP11.phx.gbl...
> This took me a little bit further. I can now actually see the tables in
in
> my program using the dmo calls. Thanks! owever, when I try to access the
> individual columns, I continue to get errors such as:
> EXCEPTION] 20040520 08:37:30.955 (sqlserver.myObject) Error retrieving
info
> from test2: Server user 'mymachine\mylogin' is not a valid user in
database[vbcol=seagreen]
> 'test2'.
> Again, this all works fine using SQL Personal Edition.
> Neil
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> Was MSDE installed with the network protocols enabled? With SP3a they are
> turned off by default.
> Jim
> "Neil W." <neilw@.netlib.com> wrote in message
> news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> but
can't[vbcol=seagreen]
Personal,
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection[vbcol=seagreen]
or
> Object
>
>

Login to MSDE on Win 98

Hi all,

I have the the problem with logging to MSDE using SQL-DMO interface. On the
W2K with LoginSecure=TRUE everything is OK (connect is made without any
parameters)

But when LoginSecure=FALSE (on Win 98)

connect( 'instance_name', 'sa', 'sa_password' )

then it returns error message :

Login failed for user 'sa'. Reason : Not associated with a trusted SQL

I install MSDE on Win 98 with parameters :

INSTANCENAME='instance_name'
SECURITYMODE=SQL
SAPWD='sa_password'

Server runs with given instance name.

Who knows why I can not login to MSDE ?

TIA,

Marek Powichrowski"Marek Powichrowski" <marekp@.telbank.pl> wrote in message
news:bv81ss$hnp$1@.korweta.task.gda.pl...
> Hi all,
> I have the the problem with logging to MSDE using SQL-DMO interface. On
the
> W2K with LoginSecure=TRUE everything is OK (connect is made without any
> parameters)
> But when LoginSecure=FALSE (on Win 98)
> connect( 'instance_name', 'sa', 'sa_password' )
> then it returns error message :
> Login failed for user 'sa'. Reason : Not associated with a trusted SQL
> I install MSDE on Win 98 with parameters :
> INSTANCENAME='instance_name'
> SECURITYMODE=SQL
> SAPWD='sa_password'
> Server runs with given instance name.
> Who knows why I can not login to MSDE ?
> TIA,
> Marek Powichrowski

The error is slightly strange, since it suggests MSDE is running in Windows
authentication mode, however that isn't supported on Windows 98. Can you
connect to the MSDE instance locally using osql, for example?

osql -S instance_name -U sa -P sa_password

If you can connect like this, then the issue is probably somewhere in your
SQLDMO code, and perhaps you could post the part which makes the connection.
If this doesn't work, then could you post the error message? And did you
install the latest MSDE version, ie. SP3a?

Simon|||>
> The error is slightly strange, since it suggests MSDE is running in
Windows
> authentication mode, however that isn't supported on Windows 98. Can you
> connect to the MSDE instance locally using osql, for example?
> osql -S instance_name -U sa -P sa_password
> If you can connect like this, then the issue is probably somewhere in your
> SQLDMO code, and perhaps you could post the part which makes the
connection.
> If this doesn't work, then could you post the error message? And did you
> install the latest MSDE version, ie. SP3a?

Cause of error was setting LoginSecure to TRUE for the connection made on
the Win 98. When I change LoginSecure to FALSE then connection ends
succesfully (for proper server name user name and password) . So I can
create database using script generated by Enterprise Manager from my
computer and can atatch existing database to MSDE without Enterprise Manager
in the easy way (few lines of code).

Thanks for your support.

Marek Powichrowski

Login to MSDE

Hi all,
I have the the problem with logging to MSDE using SQL-DMO interface. On the
W2K with LoginSecure=TRUE everything is OK (connect is made without any
parameters)
But when LoginSecure=FALSE
connect( 'instance_name', 'sa', 'sa_password' )
it returns error message :
Login failed for user 'sa'. Reason : Not associated with a trusted SQL
I install MSDE Win 98 with parameters :
INSTANCENAME='instance_name'
SECURITYMODE=SQL
SAPWD='sa_password'
Server runs with given instance name.
Who knows why I can not login to MSDE ?
TIA,
Marek PowichrowskiCheck out article 319930, especially the section entitled:
Enable Mixed Mode Authentication After Installation
to verify that MSDE is in fact running in mixed mode.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Uytkownik "Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> napisa w
wiadomoci news:KWta2ad5DHA.824@.cpmsftngxa07.phx.gbl...

quote:

---

> Check out article 319930, especially the section entitled:
> Enable Mixed Mode Authentication After Installation
> to verify that MSDE is in fact running in mixed mode.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

---

Thanks for your support.
Marek Powichrowski

Login problem: MSDE database from ASP.Net

I have created an MSDE database and successfully used it with an VB.Net
program on my desktop. Works great.
I have just begun learning ASP.Net. I created another MSDE database. But I
can't get past the login problems. "Predict" is the name of the database.
"GARY" is the Windows user account name. The error message is as follows:
Cannot open database requested in login 'Predict'. Login fails. Login failed
for user 'GARY\ASPNET'.
I used the following 2 connection strings. Neither works.
data source=(local)\VSdotNET;database=Predict;integrate d security=true
workstation id=GARY;packet size=4096;integrated security=SSPI;data
source="GARY\VSDOTNET";persist security info=False;initial catalog=Predict
Does anyone know what MSDE might be looking for here?
hi Gary,
Gary Frank wrote:
> I have created an MSDE database and successfully used it with an
> VB.Net program on my desktop. Works great.
> I have just begun learning ASP.Net. I created another MSDE database.
> But I can't get past the login problems. "Predict" is the name of
> the database. "GARY" is the Windows user account name. The error
> message is as follows:
> Cannot open database requested in login 'Predict'. Login fails. Login
> failed for user 'GARY\ASPNET'.
> I used the following 2 connection strings. Neither works.
> data source=(local)\VSdotNET;database=Predict;integrate d security=true
> workstation id=GARY;packet size=4096;integrated security=SSPI;data
> source="GARY\VSDOTNET";persist security info=False;initial
> catalog=Predict
> Does anyone know what MSDE might be looking for here?
you have to grant the account trying to log in (GARY\ASPNET) login
privileges as long as granting hin db access to the required databases..
as you can see from the raised error, using WinNT trusted authentication via
asp, you are not using the account you are logged in with, but the account
IIS is running on..
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
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3jdk15Fp8sp6U1@.individual.net...
> hi Gary,
> Gary Frank wrote:
> you have to grant the account trying to log in (GARY\ASPNET) login
> privileges as long as granting hin db access to the required databases..
> as you can see from the raised error, using WinNT trusted authentication
> via asp, you are not using the account you are logged in with, but the
> account IIS is running on..
> --
> 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
Thanks Andrea. I am using MSDE and not SQL Server. As I understand it,
MSDE does not come with comprehensive administration tools. I can't figure
out how to grant access using MSDE. Are you aware of any tools I can
download or pay for without buying SQL Server, in order to grant database
access to a Windows user account?
|||hi Gary,
Gary Frank wrote:
> Thanks Andrea. I am using MSDE and not SQL Server. As I understand
> it, MSDE does not come with comprehensive administration tools. I
> can't figure out how to grant access using MSDE. Are you aware of
> any tools I can download or pay for without buying SQL Server, in
> order to grant database access to a Windows user account?
you can use the command line tool, oSql.exe, provided with MSDE, like
described in
http://support.microsoft.com/default...EN-US;q325003, or you can
have a look at a free prj of mine, available at the link following my
sign... other tools,, both free and commercial, are listed at
http://www.microsoft.com/sql/msde/partners/ and/or
http://www.aspfaq.com/show.asp?id=2442..
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

Login problem, mystery

Hi
I have installed MSDE ver 8.00.760.03 from the package sql2desksp03 located
at go.microsoft.com/fwlink/?linkid=13962
My intention is to develop an asp.net application with Microsoft Visual
Basic.Net Standard. Already installed. Because I can't create a database
with the
standard edition I want to use MS Access 2000 project to create the tables,
but when I try to create the project using the Microsoft SQL Server database
wizard and enter my SQL Server details as follows
name=svrname
user=sa
password=correctpassword
I get the following message.
Connection failed:
SQLState:'28000'
SQL Server Error: 18452
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user 'sa'.
Reason: Not associated with a trusted SQL Server connection.
If I leave the Microsoft SQL Server database wizard user and password boxes
empty I am able to create my project but unable to create tables.
I'm on WinXP SP2 with Administrative rights. MSDE is running and my password
is definately correct - Please help :-)
hi,
SimonW wrote:
> Hi
> I have installed MSDE ver 8.00.760.03 from the package sql2desksp03
> located at go.microsoft.com/fwlink/?linkid=13962
> My intention is to develop an asp.net application with Microsoft
> Visual Basic.Net Standard. Already installed. Because I can't create
> a database with the
> standard edition I want to use MS Access 2000 project to create the
> tables, but when I try to create the project using the Microsoft SQL
> Server database
> wizard and enter my SQL Server details as follows
> name=svrname
> user=sa
> password=correctpassword
> I get the following message.
> Connection failed:
> SQLState:'28000'
> SQL Server Error: 18452
> [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user
> 'sa'. Reason: Not associated with a trusted SQL Server connection.
> If I leave the Microsoft SQL Server database wizard user and password
> boxes empty I am able to create my project but unable to create
> tables.
> I'm on WinXP SP2 with Administrative rights. MSDE is running and my
> password is definately correct - Please help :-)
MSDE installs by default only allowing trusted conections... you can modify
this behavior bot at install time, providing the
SECURITYMODE=SQL
parameter to the setup.exe boostrap installer or later, at runtime, hacking
the Windows registry as described in
http://support.microsoft.com/default...n-us;285097... stop and
restart the server..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you very much Andrea :-)
"Andrea Montanari" wrote:

> hi,
> SimonW wrote:
> MSDE installs by default only allowing trusted conections... you can modify
> this behavior bot at install time, providing the
> SECURITYMODE=SQL
> parameter to the setup.exe boostrap installer or later, at runtime, hacking
> the Windows registry as described in
> http://support.microsoft.com/default...n-us;285097... stop and
> restart the server..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>

Login permisions

Hi!
I've just moved a mdb to MSDE without problem. The problem comes when I try
to open the tables I have attached via ODBC. Tables are in MSDE and I call
them from Access.
I get an error telling me that the user is not allowed to read (error
18456). Following that message, I get a login screen. If I enter the sa user
password it connects and allows me to see the table contents.
I use MSDE Manager to manage the server, but any other option is welcomed if
I should have more control over it.
What I would like to do is that no password is asked when the users run the
Access database.
Thanks in advance.
Xavier Boneu
hi Xavier,
Xavier Boneu wrote:
> Hi!
> I've just moved a mdb to MSDE without problem. The problem comes when
> I try to open the tables I have attached via ODBC. Tables are in MSDE
> and I call them from Access.
> I get an error telling me that the user is not allowed to read (error
> 18456). Following that message, I get a login screen. If I enter the
> sa user password it connects and allows me to see the table contents.
> I use MSDE Manager to manage the server, but any other option is
> welcomed if I should have more control over it.
> What I would like to do is that no password is asked when the users
> run the Access database.
> Thanks in advance.
you have then to specify, in the ODBC management console, to use integrated
security... you have then to manage your Windows account to grant them
access to the MSDE instance, using sp_grantlogin system stored procedure (
http://msdn.microsoft.com/library/de...ga-gz_8dri.asp )
and grant each login, or WinNT group (if you can manage them at group level
instead of single account level you will gain some freedom) access to each
desired database via sp_grantdbaccess system stored procedure (
http://msdn.microsoft.com/library/de...ga-gz_290z.asp )
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.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 your info. I'll try it and post back the results.
Xavier Boneu
"Andrea Montanari" wrote:

> hi Xavier,
> Xavier Boneu wrote:
> you have then to specify, in the ODBC management console, to use integrated
> security... you have then to manage your Windows account to grant them
> access to the MSDE instance, using sp_grantlogin system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_8dri.asp )
> and grant each login, or WinNT group (if you can manage them at group level
> instead of single account level you will gain some freedom) access to each
> desired database via sp_grantdbaccess system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_290z.asp )
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||Andrea,
I am trying what you suggest but it works only partialy.
I've issued the sp_grantlogin and sp_grantdbaccess but when I try to execute
a query from a client computer I get an error saying I have no permission to
SELECT the object 'mytable', database 'mydtabase' , owner 'dbo'. (#229)
What I wrote was:
EXEC sp_grantlogin 'companyname\username'
EXEC sp_grantdbaccess 'companyname\username'
but something is still missing...
Could you give me a hand with this error?
Thank you.
Xavier
Xavier Boneu
"Andrea Montanari" wrote:

> hi Xavier,
> Xavier Boneu wrote:
> you have then to specify, in the ODBC management console, to use integrated
> security... you have then to manage your Windows account to grant them
> access to the MSDE instance, using sp_grantlogin system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_8dri.asp )
> and grant each login, or WinNT group (if you can manage them at group level
> instead of single account level you will gain some freedom) access to each
> desired database via sp_grantdbaccess system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_290z.asp )
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||Andrea,
I am trying what you suggest but it works only partialy.
I've issued the sp_grantlogin and sp_grantdbaccess but when I try to execute
a query from a client computer I get an error saying I have no permission to
SELECT the object 'mytable', database 'mydtabase' , owner 'dbo'. (#229)
What I wrote was:
EXEC sp_grantlogin 'companyname\username'
EXEC sp_grantdbaccess 'companyname\username'
but something is still missing...
Could you give me a hand with this error?
Thank you.
Xavier
Xavier Boneu
"Andrea Montanari" wrote:

> hi Xavier,
> Xavier Boneu wrote:
> you have then to specify, in the ODBC management console, to use integrated
> security... you have then to manage your Windows account to grant them
> access to the MSDE instance, using sp_grantlogin system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_8dri.asp )
> and grant each login, or WinNT group (if you can manage them at group level
> instead of single account level you will gain some freedom) access to each
> desired database via sp_grantdbaccess system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_290z.asp )
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Xavier,
Xavier Boneu wrote:
> Andrea,
> I am trying what you suggest but it works only partialy.
> I've issued the sp_grantlogin and sp_grantdbaccess but when I try to
> execute a query from a client computer I get an error saying I have
> no permission to SELECT the object 'mytable', database 'mydtabase' ,
> owner 'dbo'. (#229)
the security goes on :D
as you add a database user to your preferred database, he/she will only be
member of the Public database role, and usually that role has no kind of
access to object's data...
you can so grant the desired permission (SELECT/INSERT/UPDATE/DELETE) to the
Public database role (not recommended at all) or make him/her member of
specific builtin (or user defined) database role(s) [
http://msdn.microsoft.com/library/de..._addp_4boy.asp ,
http://msdn.microsoft.com/library/de..._addp_33s5.asp ]
...
that's to say you can make him/her member of the default db_datareader role
to permit only data browsing, eventually db_datawriter to allow data
modification, or grant him/her only specific privilegs on specific tables...
usually you make him/her member of custom db roles granting only EXEC
privileges to defined stored procedures for data retrevial/modification, and
/or SELECT/INSERT/UPDATE/DELETE privileges on user define views as it's not
a best practice to allow direct access to base table (please have a look at
http://msdn.microsoft.com/library/de...ga-gz_8odw.asp)
you can start reading at
http://www.microsoft.com/technet/pro...sp3sec00.mspx,
http://www.developer.com/tech/article.php/721441
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thanks for your help. By now, I have granted db_datareader and db_datawriter
permisions to all the users. This way they can work and now, with more time,
I will read the information you have suggested and 'study' to manage MSDE
better.
Thanks a lot!!!
Xavier Boneu
"Andrea Montanari" wrote:

> hi Xavier,
> Xavier Boneu wrote:
> the security goes on :D
> as you add a database user to your preferred database, he/she will only be
> member of the Public database role, and usually that role has no kind of
> access to object's data...
> you can so grant the desired permission (SELECT/INSERT/UPDATE/DELETE) to the
> Public database role (not recommended at all) or make him/her member of
> specific builtin (or user defined) database role(s) [
> http://msdn.microsoft.com/library/de..._addp_4boy.asp ,
> http://msdn.microsoft.com/library/de..._addp_33s5.asp ]
> ...
> that's to say you can make him/her member of the default db_datareader role
> to permit only data browsing, eventually db_datawriter to allow data
> modification, or grant him/her only specific privilegs on specific tables...
> usually you make him/her member of custom db roles granting only EXEC
> privileges to defined stored procedures for data retrevial/modification, and
> /or SELECT/INSERT/UPDATE/DELETE privileges on user define views as it's not
> a best practice to allow direct access to base table (please have a look at
> http://msdn.microsoft.com/library/de...ga-gz_8odw.asp)
> you can start reading at
> http://www.microsoft.com/technet/pro...sp3sec00.mspx,
> http://www.developer.com/tech/article.php/721441
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>