Greetings,
I am unable to log onto the MS SQL Server 2000 using my Windows Login name
and Password from the MS command prompt program locally on a new test
installation Windows Enterprise Server 2003 system. However, if I use an
SQL Server Login and Password, it works every time. The command string I am
using is:
"osql.exe -S (local) -U login_name -P password"
When successful (SQL Authentication), an SQL command prompt is returned.
When unsuccessful (Windows Authentication), the following message is
received:
"Login failed for user login_name"
The SQL Server 2000 installation is enabled for both Windows and SQL
authentication and has been updated to the latest service packs.
The installation is a new test set up to experiment with Web Services using
MS SQL Server. The original problem was the installation of the
"wscrRecordStore" demonstration data base from the book, Programming
Microsoft .NET XML WEB SERVICES, by Foggon et al. However, the problem was
reduced to the simple command line issue above in order to troubleshoot it.
Since I am also the Windows and SQL Server system administrator, I have
tried a wide variety of different login strategies. Only users set up with
SQL Authentication seem to be able to access the SQL Server programatically.
What's the problem? This seems to be a straight forward but stubborn
failure.
Richard Scott
Critical Connections, Inc.To establish a Windows authenticated connection from OSQL, you need to
specify the -E parameter instead of -U and -P. This will use the account of
the currently logged in user. For example:
OSQL -S (local) -E
> Only users set up with SQL Authentication seem to be able to access the
SQL Server programatically.
Your connection string needs to specify the type of authentication desired.
Assuming you are using SqlClient:
Windows authentication:
Data Source=MyServer;Integrated Security=SSPI;Initial
Catalog=MyDatabase
SQL Server authentication:
Data Source=MyServer;User ID=MyLogin;Password=MyPassword;Initial
Catalog=MyDatabase
Also, see the article below for additional information on establishing a
trusted connection from ASP.NET:
http://support.microsoft.com/defaul...989&Product=sql
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Scott" <rtscott@.pacbell.net> wrote in message
news:6XL6c.12481$zh1.7776@.newssvr27.news.prodigy.com...
> Greetings,
> I am unable to log onto the MS SQL Server 2000 using my Windows Login name
> and Password from the MS command prompt program locally on a new test
> installation Windows Enterprise Server 2003 system. However, if I use an
> SQL Server Login and Password, it works every time. The command string I
am
> using is:
> "osql.exe -S (local) -U login_name -P password"
> When successful (SQL Authentication), an SQL command prompt is returned.
> When unsuccessful (Windows Authentication), the following message is
> received:
> "Login failed for user login_name"
> The SQL Server 2000 installation is enabled for both Windows and SQL
> authentication and has been updated to the latest service packs.
> The installation is a new test set up to experiment with Web Services
using
> MS SQL Server. The original problem was the installation of the
> "wscrRecordStore" demonstration data base from the book, Programming
> Microsoft .NET XML WEB SERVICES, by Foggon et al. However, the problem
was
> reduced to the simple command line issue above in order to troubleshoot
it.
> Since I am also the Windows and SQL Server system administrator, I have
> tried a wide variety of different login strategies. Only users set up
with
> SQL Authentication seem to be able to access the SQL Server
programatically.
> What's the problem? This seems to be a straight forward but stubborn
> failure.
> Richard Scott
> Critical Connections, Inc.
>|||Not sure if this applies to you, but vefiry that the user who is attempting
to connect actually has a login established in SQL Server. You can do this
by expanding the Security folder in Enterprise Manager and looking at the
Logins.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Showing posts with label program. Show all posts
Showing posts with label program. Show all posts
Saturday, February 25, 2012
Monday, February 20, 2012
No tables in merge replication
Hi, I have setup a publication that my program on my CE device will
synchronize to. It connects and downloads the database fine, and the database
file shows that data has been downloaded. But when I try to run SQL
statements against the database, it says that the tables are not there. I
also used the Query Analyzer that is installed on the machine when using
vs.net, and it does not show any tables, either. I was wondering if there was
something that I was doing wrong when setting up the publication.
Thanks
Daniel Robinson
This is highly abnormal. Can you possibly post your code here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel Robinson" <DanielRobinson@.discussions.microsoft.com> wrote in
message news:72C63D6C-6F68-416E-AC16-8BE0A8B94CD9@.microsoft.com...
> Hi, I have setup a publication that my program on my CE device will
> synchronize to. It connects and downloads the database fine, and the
database
> file shows that data has been downloaded. But when I try to run SQL
> statements against the database, it says that the tables are not there. I
> also used the Query Analyzer that is installed on the machine when using
> vs.net, and it does not show any tables, either. I was wondering if there
was
> something that I was doing wrong when setting up the publication.
> Thanks
> Daniel Robinson
|||I don't think it's a problem with my code. We had a publication setup for
another database, and that was working. All I did in the code was change the
publication name.
Daniel
"Hilary Cotter" wrote:
> This is highly abnormal. Can you possibly post your code here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Daniel Robinson" <DanielRobinson@.discussions.microsoft.com> wrote in
> message news:72C63D6C-6F68-416E-AC16-8BE0A8B94CD9@.microsoft.com...
> database
> was
>
>
|||Here is the SQL that I used to create the publication:
EXEC sp_replicationdboption @.dbname='daniel', @.optname='merge publish',
@.value='true'
EXEC sp_addmergepublication @.publication='uadd_AMSSchool_Daniel',
@.allow_pull='true', @.allow_push='false',
@.allow_anonymous='true', @.enabled_for_internet='true',
@.snapshot_in_defaultfolder='false', @.alt_snapshot_folder='C:\SqlPublication',
@.compress_snapshot='false', @.sync_mode='character', @.retention=60,
@.conflict_retention=60, @.allow_synctoalternate='true',
@.keep_partition_changes='true'
EXEC sp_addpublication_snapshot @.publication='uadd_AMSSchool_Daniel',
@.frequency_type=8, @.frequency_interval=1,
@.frequency_subday=1, @.frequency_subday_interval=1,
@.frequency_recurrence_factor=1
EXEC sp_grant_publication_access @.publication='uadd_AMSSchool_Daniel',
@.login='sa'
EXEC sp_addmergearticle @.publication='uadd_AMSSchool_Daniel',
@.article='mytable', @.source_object='mytable', @.source_owner='dbo',
@.type='table', @.destination_owner='dbo', @.column_tracking='true',
@.force_invalidate_snapshot=1, @.vertical_partition='true'
EXEC sp_mergearticlecolumn @.publication='uadd_AMSSchool_Daniel',
@.article='mytable', @.column='myname', @.operation='add',
@.schema_replication='true', @.force_invalidate_snapshot=1,
@.force_reinit_subscription=1
EXEC sp_mergearticlecolumn @.publication='uadd_AMSSchool_Daniel',
@.article='mytable', @.column='myint', @.operation='add',
@.schema_replication='true', @.force_invalidate_snapshot=1,
@.force_reinit_subscription=1
I do get a warning that only Subscribers running SQL Server 2000 can
synchronize with the publication...and I am running sqlCE. You think this
might be causing a problem?
Thanks
Daniel
"Hilary Cotter" wrote:
> This is highly abnormal. Can you possibly post your code here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Daniel Robinson" <DanielRobinson@.discussions.microsoft.com> wrote in
> message news:72C63D6C-6F68-416E-AC16-8BE0A8B94CD9@.microsoft.com...
> database
> was
>
>
synchronize to. It connects and downloads the database fine, and the database
file shows that data has been downloaded. But when I try to run SQL
statements against the database, it says that the tables are not there. I
also used the Query Analyzer that is installed on the machine when using
vs.net, and it does not show any tables, either. I was wondering if there was
something that I was doing wrong when setting up the publication.
Thanks
Daniel Robinson
This is highly abnormal. Can you possibly post your code here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel Robinson" <DanielRobinson@.discussions.microsoft.com> wrote in
message news:72C63D6C-6F68-416E-AC16-8BE0A8B94CD9@.microsoft.com...
> Hi, I have setup a publication that my program on my CE device will
> synchronize to. It connects and downloads the database fine, and the
database
> file shows that data has been downloaded. But when I try to run SQL
> statements against the database, it says that the tables are not there. I
> also used the Query Analyzer that is installed on the machine when using
> vs.net, and it does not show any tables, either. I was wondering if there
was
> something that I was doing wrong when setting up the publication.
> Thanks
> Daniel Robinson
|||I don't think it's a problem with my code. We had a publication setup for
another database, and that was working. All I did in the code was change the
publication name.
Daniel
"Hilary Cotter" wrote:
> This is highly abnormal. Can you possibly post your code here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Daniel Robinson" <DanielRobinson@.discussions.microsoft.com> wrote in
> message news:72C63D6C-6F68-416E-AC16-8BE0A8B94CD9@.microsoft.com...
> database
> was
>
>
|||Here is the SQL that I used to create the publication:
EXEC sp_replicationdboption @.dbname='daniel', @.optname='merge publish',
@.value='true'
EXEC sp_addmergepublication @.publication='uadd_AMSSchool_Daniel',
@.allow_pull='true', @.allow_push='false',
@.allow_anonymous='true', @.enabled_for_internet='true',
@.snapshot_in_defaultfolder='false', @.alt_snapshot_folder='C:\SqlPublication',
@.compress_snapshot='false', @.sync_mode='character', @.retention=60,
@.conflict_retention=60, @.allow_synctoalternate='true',
@.keep_partition_changes='true'
EXEC sp_addpublication_snapshot @.publication='uadd_AMSSchool_Daniel',
@.frequency_type=8, @.frequency_interval=1,
@.frequency_subday=1, @.frequency_subday_interval=1,
@.frequency_recurrence_factor=1
EXEC sp_grant_publication_access @.publication='uadd_AMSSchool_Daniel',
@.login='sa'
EXEC sp_addmergearticle @.publication='uadd_AMSSchool_Daniel',
@.article='mytable', @.source_object='mytable', @.source_owner='dbo',
@.type='table', @.destination_owner='dbo', @.column_tracking='true',
@.force_invalidate_snapshot=1, @.vertical_partition='true'
EXEC sp_mergearticlecolumn @.publication='uadd_AMSSchool_Daniel',
@.article='mytable', @.column='myname', @.operation='add',
@.schema_replication='true', @.force_invalidate_snapshot=1,
@.force_reinit_subscription=1
EXEC sp_mergearticlecolumn @.publication='uadd_AMSSchool_Daniel',
@.article='mytable', @.column='myint', @.operation='add',
@.schema_replication='true', @.force_invalidate_snapshot=1,
@.force_reinit_subscription=1
I do get a warning that only Subscribers running SQL Server 2000 can
synchronize with the publication...and I am running sqlCE. You think this
might be causing a problem?
Thanks
Daniel
"Hilary Cotter" wrote:
> This is highly abnormal. Can you possibly post your code here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Daniel Robinson" <DanielRobinson@.discussions.microsoft.com> wrote in
> message news:72C63D6C-6F68-416E-AC16-8BE0A8B94CD9@.microsoft.com...
> database
> was
>
>
Labels:
connects,
database,
databasefile,
device,
downloads,
merge,
microsoft,
mysql,
oracle,
program,
publication,
replication,
server,
setup,
sql,
tables,
willsynchronize
Subscribe to:
Posts (Atom)