Cloud Server MS SQL Database Issues



  • I consider myself pretty good at SQL work, but I'm lousy at the IT side of it all. Hoping someone can provide some advice/help about the database setup of one of our projects.

    Ok, so we have a couple of MS SQL servers set up in the Amazon EC2 cloud. We have a need to:

    1) Frequently (multiple times daily) connect to databases to run ad-hoc queries
    2) Occasionally (5-10 times a month) copy a database from one of the servers to the other

    Currently, what we're doing is running the SQL Servers in "SQL Server Login" mode, to enable us to log into the servers from our work desktops. To copy databases from one server to the other, we set up a share drive and are simply backing-up the database on one server, and restoring it on the other.

    Here are the problems we have:
    1) Copying the database requires someone to remote into the server, which makes me paranoid that they're going to screw something up. Also the fileshare between the two EC2 instances bugs me, even though it's disconnected 99% of the time
    2) Each DB server has its own copy of each user... which causes problems when copying databases between servers. Apparently, the users get copied with the database, and since those same usernames already exist on the destination server it causes a conflict-- after restoring the database, we have to dive into it and delete a few user accounts, then go into the SQL Server config and "map" those names to the new database. I'd love to make copied databases "just work" so no manual effort was involved after the DB restore was complete
    3) Changing usernames/passwords needs to be done separately for each DB server, which is a pain

    We have another (much, much) larger team with a similar setup, and they solved most of their problems by creating an Active Directory in their EC2 instances, and then only doing ad-hoc queries while logged-on to an EC2 instance. Unfortunately, I don't really know jack about creating an AD, and it seems overkill since we only have 2 servers. Plus we'd have to pay more for EC2 instances for everybody who does ad-hoc queries.

    The end goal here is to be able to automate the database copying, so nobody needs administrative permissions to either of the DB EC2 instances. (Except the actual admin, of course.)

    So:

    1) Is there a way to get two SQL Servers to share their username/password databases without setting up a domain?
    2) Is there a better way to copy databases from one server to another than the backup/restore functionality?
    3) Are there any security implications to either of those options? Are there any security implications to using "SQL Server Authentication", for that matter?

    Any help appreciated.



  • @blakeyrat said:

    1) Is there a way to get two SQL Servers to share their username/password databases without setting up a domain?

    You can script an export of users and synchronize that (but not passwords, obviously). Also, there is a standard stored proc to fix the issue of users having different GUIDs in different instances. But all in all, I'd say, go with the AD solution.

    @blakeyrat said:

    2) Is there a better way to copy databases from one server to another than the backup/restore functionality?

    The obvious solution is DTS (is it still called that way?). SQL Server has traditionally been great at synchronizing across databases and servers.

    @blakeyrat said:

    3) Are there any security implications to either of those options? Are there any security implications to using "SQL Server Authentication", for that matter?

    AFAIK the most common security implication of SQL Server Authentication is the way it's used, with people storing usernames/passwords plaintext in connectionstrings. Integrated security makes it easier for sloppy coders.

    @MSDN said:

    Windows Authentication offers advanced security features over SQL Server Authentication. These features are a facet of the Windows 2000 Security Policies. Features include: password expirations, password attributes, auditing, and account lockouts, and mutual authentication using Kerberos.



  • 1) Why EC2 and not Azure?

    2) Why not Replication?

    3) Yes, "SQL Server Authentication" should (well almost) never be used due to a wide variety of security issues.



  • @b-redeker said:

    But all in all, I'd say, go with the AD solution.

    Foo. Ok, follow-up... if our computers are already in our company AD, can we also join them to an EC2-based AD? Or do we have to do all our work on EC2 instances, like our colleagues? (Because the latter is going to be a huge PITA.)

    @b-redeker said:

    The obvious solution is DTS (is it still called that way?). SQL Server has traditionally been great at synchronizing across databases and servers.

    Yeah; I know the gap here is the gap in my own knowledge. We tried to enable "SQL Server Agent" and using its "Copy Database" functionality, but it didn't work because the two EC2 instances couldn't see each other. This would probably be solved if we had them both in the same domain, though.

    @b-redeker said:

    AFAIK the most common security implication of SQL Server Authentication is the way it's used, with people storing usernames/passwords plaintext in connectionstrings. Integrated security makes it easier for sloppy coders.

    Our connectionstrings are all on AWS servers, so there's no way for a user to access them unless they crack RDS' encryption. So I'm not too worried about that specifically.

    @TheCPUWizard said:

    1) Why EC2 and not Azure?

    1. Because the app was previously hosted on physical servers, and we didn't want to re-write it

      2) Because we want the flexibility to switch Cloud providers whenever we want, or whenever we find a better deal than Amazon. Once your app is coded for Azure, it'll only run on Azure

    @TheCPUWizard said:

    2) Why not Replication?

    The copies are snapshots, we don't want them to stay in-sync. To my (possibly flawed) knowledge, replication only works when you want the two copies of the database to be continually in-sync.

    @TheCPUWizard said:

    3) Yes, "SQL Server Authentication" should (well almost) never be used due to a wide variety of security issues.

    Well, I understand the queries and results are sent in plaintext (I've looked at them using NetMon, when I first set this up), but the login is pretty damned encypted, so I don't see that as being extremely important.

    Can you enumerate why it's insecure? (Other than the "connection strings stored in app" issue b-redeker mentioned which doesn't apply to us.)

    Thanks.



  • @blakeyrat said:

    @b-redeker said:
    But all in all, I'd say, go with the AD solution.
    Foo. Ok, follow-up... if our computers are already in our company AD, can we also join them to an EC2-based AD? Or do we have to do all our work on EC2 instances, like our colleagues? (Because the latter is going to be a huge PITA.)
    Create a trust between the domains.  This way you can log into your company domain and still be able to access the servers in the other domain.@blakeyrat said:
    @b-redeker said:
    The obvious solution is DTS (is it still called that way?). SQL Server has traditionally been great at synchronizing across databases and servers.
    Yeah; I know the gap here is the gap in my own knowledge. We tried to enable "SQL Server Agent" and using its "Copy Database" functionality, but it didn't work because the two EC2 instances couldn't see each other. This would probably be solved if we had them both in the same domain, though.
    You might want to consider creating a virtual backup device that streams backup data from one server to the other.  This page is a starting reference: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&DisplayLang=en

    The idea is that you do a backup on server 1 with a destination of the custom backup device input interface and a restore on server 2 with a source of the custom backup device output interface.  The custom backup device simply pumps data from its input interface to its output interface.



  • @blakeyrat said:

    Once your app is coded for Azure, it'll *only* run on Azure
    That's why I'm not even considering Azure at the moment.  If Microsoft let me run my own Azure instance, then I could at least consider writing everything for it and moving things in and out of the cloud as I see fit.



  • @Jaime said:

    Create a trust between the domains.  This way you can log into your company domain and still be able to access the servers in the other domain.

    Oh man, I look forward to trying to get our horrible new French IT people to do that...

    @Jaime said:

    You might want to consider creating a virtual backup device that streams backup data from one server to the other.  This page is a starting reference: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&DisplayLang=en

    The idea is that you do a backup on server 1 with a destination of the custom backup device input interface and a restore on server 2 with a source of the custom backup device output interface.  The custom backup device simply pumps data from its input interface to its output interface.

    Maybe I'm dense, but I don't get the point of that... isn't that just exactly what I'm doing now, plus one extra step?



  • @blakeyrat said:

    @Jaime said:

    You might want to consider creating a virtual backup device that streams backup data from one server to the other.  This page is a starting reference: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&DisplayLang=en

    The idea is that you do a backup on server 1 with a destination of the custom backup device input interface and a restore on server 2 with a source of the custom backup device output interface.  The custom backup device simply pumps data from its input interface to its output interface.

    Maybe I'm dense, but I don't get the point of that... isn't that just exactly what I'm doing now, plus one extra step?
    If you do this, you won't have to create a file share, the data will be streamed from server to server.


  • As for #1, I typically run the following per user to resync the logins after a restore from another server, EXEC sp_change_users_login @Action='update_one', @UserNamePattern='db_login_name', @LoginName='instance_login_name';

    As for #2, you might want to consider a DB sync tool such as RedGate's Comparison Bundle, it does both schema comparison and data comparison.  If you use these tools, then you won't need to worry about security resyncs because the destination db will be setup once and only the changes are pushed (unless you add a new user of course).  It also has command line interface and framework to build custom comparison solutions.



  • @lpope187 said:

    As for #1, I typically run the following per user to resync the logins after a restore from another server, EXEC sp_change_users_login @Action='update_one', @UserNamePattern='db_login_name', @LoginName='instance_login_name';

    Thanks for letting me know about that sproc, I'll check it out and see if it fixes our user problems.



  • Huh, weird. I used to have a script that looped through a cursor and did sp_change_users_login 'auto_fix' but now I look it up and see:

    Auto_Fix Links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins. You should check the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.

    When using Auto_Fix, you must specify user and password; login must be NULL. user must be a valid user in the current database.

    The weird part is that I just looked it up and I never provided the password, yet it worked fine.

    -- loop through user names and add them
    declare crs_user_names cursor
    for
    	select name from sysusers where status <> 0 and name <> 'dbo'
    

    -- select the first user and enter the loop
    open crs_user_names
    declare @this_user sysname
    set @this_user = ''
    fetch next from crs_user_names into @this_user

    -- only if no errors
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
    -- now we have the user and the login, connect the two
    exec sp_change_users_login 'auto_fix', @this_user
    END
    FETCH NEXT FROM crs_user_names INTO @this_user
    END

    -- we're done, close it all:
    CLOSE crs_user_names
    DEALLOCATE crs_user_names



  • If it requires the password, it won't work for me, since I don't know half the users' passwords. I just know my own, and the one our data collection server uses.

    Thanks for the query. Next time I copy a database, I'll give it a try.



  • Stupid domain question:

    If I set up a domain in EC3, and that domain has a fileshare, and I connect to the fileshare from my office's completely different domain, is that considered a trust relationship?

    If so, once the fileshare is connected, SSMS should be able to create a connection to the DB server based on the already-trusted filesharing connection?

    Basically, I'm looking for a way to:
    1) Set up a domain in EC3 to solve the problem of sharing users between different database servers
    2) Still be able to connect to the database servers using SSMS from our work desktops
    3) Avoid interaction with our terrible French IT department

    One more question: an article I read while researching this said it was a bad idea to put the ASP.net servers in the same domain as the database servers. Is there any truth to this?



  • Not a stupid question, but the answer is NO... A "Trust" relationship is set up at the Active Drectory Level to allow credentials that have been verified in one domain to be used in another. It involves configuring your domain controller(s) appropriately.



  • @TheCPUWizard said:

    Not a stupid question, but the answer is NO... A "Trust" relationship is set up at the Active Drectory Level to allow credentials that have been verified in one domain to be used in another. It involves configuring your domain controller(s) appropriately.

    Damn. Thanks.



  • @blakeyrat said:

    question: an article I read while researching this said it was a bad idea to put the ASP.net servers in the same domain as the database servers. Is there any truth to this?

    Some truth.  The theory is that if the web server is compromised, the attacker has access to a computer on the domain and can enumerate domain accounts.  This might give them a small leg up when attacking the database server.  In really isn't that big of a leg up since the database server name has to be in the configuration of the web application anyways.

    The likely reality is that if someone compromises the web server, they'll modify the application itself to dump the content of the database and they won't have to attack it directly.

    I would worry about getting rid of those "SQL Server Authentication" accounts before worrying about this.



  • I agree with Jamie. FWIW, avoiding the attack vector he mentioned is one of the reasons to only allow access to the DB via stored procedures, and then only give rights to the account used by the WebServer to these specific procedures. Dumping is still possible, but it is ALOT more work. Depending on the value of the data, and the motivation of the attacker, it may limit the damages.



  • @Jaime said:

    The likely reality is that if someone compromises the web server, they'll modify the application itself to dump the content of the database and they won't have to attack it directly.

    I would worry about getting rid of those "SQL Server Authentication" accounts before worrying about this.

    Thanks.

    Problem is, I can't get rid of those SQL Server Authentication accounts until I figure out something feasible to replace them with... and I'm not even close to there yet. I mean, I'll engage our IT people, but I already know there's no way in frosty hell they'll create a trust relationship between our domain and the one on EC2, or, alternatively, if they do it'll cost us $75k+ from our budget.



  • Who needs THEM....it only needs to be one way.



  • @TheCPUWizard said:

    Who needs THEM....it only needs to be one way.

    ? Care to go into more detail? Are you saying I don't need to engage our useless IT?



  • Possibly....contact me directly [about 10 seconds in google maps to my real identity]



  • @TheCPUWizard said:

    Possibly....contact me directly [about 10 seconds in google maps to my real identity]

    What are you a spy or something? The hummingbird flies at midnight!



  • @blakeyrat said:

    @TheCPUWizard said:

    Who needs THEM....it only needs to be one way.

    ? Care to go into more detail? Are you saying I don't need to engage our useless IT?
    Creating a one way trust requires modifications to both domains.... http://technet.microsoft.com/en-us/library/cc756735(WS.10).aspx


  • @Jaime said:

    @blakeyrat said:

    @TheCPUWizard said:

    Who needs THEM....it only needs to be one way.

    ? Care to go into more detail? Are you saying I don't need to engage our useless IT?
    Creating a one way trust requires modifications to both domains.... http://technet.microsoft.com/en-us/library/cc756735(WS.10).aspx

    Yeah, I saw that. I have a private conversation going with TheCPUWizard, but after talking with him and some co-workers, I might just be stuck here. I think the best I can do might be to create a domain in the EC2 environment, but keep "SQL Server Authentication" turned on as well. But I'm still looking into it.


Log in to reply