SQL replication..

Discussion in 'Software' started by garyb, Jun 20, 2008.

  1. garyb

    garyb Byte Poster

    179
    2
    22
    Firstly I apologise if I am in the wrong forum, cant find one specific to DBs.

    I have a SQL 2000 database on our server and intend to roll out a desktop app with MSDE 2000 backend to 300 clients enabling them to work offline then sync with our SQL server. I seem to be stuck at step one! In testing I have created an MSDE instance on my machine, created a security login with sys admin right in MSDE but I cannot change the service start account as it says it doesnt exist! Its correct it doesnt exist on the local computer becuase I wouldnt have access to them to create these accounts!

    Am I missing something here!

    Cheers
     
    WIP: MCSA 2003
  2. onoski

    onoski Terabyte Poster

    3,120
    51
    154
    It sounds and comes across as a permission issue, how have you configured the local account to integrate with the SQL database?

    I think you might have to create a group first in the SQL server and add users to that group either using their AD integrated logon or separately.
     
    Certifications: MCSE: 2003, MCSA: 2003 Messaging, MCP, HNC BIT, ITIL Fdn V3, SDI Fdn, VCP 4 & VCP 5
    WIP: MCTS:70-236, PowerShell
  3. garyb

    garyb Byte Poster

    179
    2
    22
    Thanx for the reply, I have started from scratch documenting the steps:

    1 - Create local user account on MSDE machine with full admin rights
    2 - Assign that user as logon account in services for MSDE and MSDE agent
    3 - Created same account in MSDE security tree with sysadmin rights on all DBs
    4 - Create same user account on remote SQL server with full admin rights
    5 - Assign that user as logon account in services for SQL and SQL agent
    6 - Created same account in SQL security tree with sysadmin rights on all DBs
    7 - On SQL server attempt new registraion to MSDE instance on my machine. using Windows auth as a connection test it fails saying incorrect password. If I try using sql account password it says:

    Login failed for user 'sqladmin'. The user is not associated with a trusted SQL Server connection.

    This is odd, I connect to other SQL servers no problem?
     
    WIP: MCSA 2003
  4. garyb

    garyb Byte Poster

    179
    2
    22
    Update:
    I sorted this now, the MSDE had been installed in Windfows only mode, change it to mixed in the registry and all is good....for now!

    Has anyone ever down this before? Will I need to assign login details for all 300 subscribers? Also I guess I must roll this out without end users knowing the SQL login details, otherwise anyone could simply reuse the password to connect and download the database?

    Thanx
     
    WIP: MCSA 2003
  5. garyb

    garyb Byte Poster

    179
    2
    22
    Hi,
    I have been doing further testing with this and lots of questions are popping up..

    I have been doing further testing with this and lots of questions are popping up, I wonder if anyone can help?

    1 - The remote clients will not be connected to the main SQL via VPN, do I have to leave a port open for SQL from WAN to LAN for replication? If so I dont like the look of this?
    2 - As I understand MSDE2000 can only be installed by local admin and msi installs default instance. If this is true it appears I will need to visit every client to configure their machines, add local sqladmin accounts etc... seems harsh..
    3 - Our database will be stored locally on a user laptop, MSDE offers no method to lockdown so we could be in big trouble should the laptop go missing? Again seems an oversight..
    4 - Has anyone used 2005 express or better still 2005 compact edition? These both seem to offer better local security and the compact version can be rolled out at 2mb. Would appreciate any opinions as I will need to upgrade the production server to 2005 also if this method is indeed the best route..
     
    WIP: MCSA 2003

Share This Page

Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.