locked
SQL back-end access RRS feed

  • Question

  • Hi,

    I am DBA and would like to know more about secure SQL access by .NET applications. Currently we use SQL authentication and SQL accounts for back-end server access from front-end .NET applications. We would like to move to Domain service accounts authentication. As I need to provide some recommendations to my management could you please provide more information how this usually is implement in professional way.

    In perfect situation we would like to avoid using passwords and use service accounts instead. But if that is complicated we could use regular domain accounts, but in that case is there any .NET framework app that manages these accounts that we could enter password once without possibility to allow developers to know password from back-end service accounts.

    Main issue here to not allow access to back-end servers using front-end service accounts.

    Thanks

    Thursday, September 3, 2015 2:34 PM

Answers

  • Hello,

    Yes you can use service accounts. You can even use machine accounts, which works great if you want to lock them down by literal physical machines. The process is like anything, you turn off SQL account, add only domain accounts.

    Then merely add the machine account and or service account you want, and assign it privs.

    I am not sure what you mean by not allow access to back-end servers using front-end service accounts.

    Are you saying you have dual-homed SQL Servers (bad idea), that are seen from a front-end and back-end nic?

    Is the front end nic on a domain that is also connected to the internet (even if that IP is per say not exposed)?

    Better to have your "app" machines dual homed and your SQL only homed to your internal back end nic address. Then use service / machine level accounts.

    The thing to take into account though is, be careful if you are running services like IIS, under an account that has super access to your data, but is also visible from the front end (internet) or anywhere else.

    Cheers

    • Marked as answer by jori5 Monday, September 21, 2015 7:24 AM
    Friday, September 4, 2015 1:57 AM

All replies

  • Hello,

    Yes you can use service accounts. You can even use machine accounts, which works great if you want to lock them down by literal physical machines. The process is like anything, you turn off SQL account, add only domain accounts.

    Then merely add the machine account and or service account you want, and assign it privs.

    I am not sure what you mean by not allow access to back-end servers using front-end service accounts.

    Are you saying you have dual-homed SQL Servers (bad idea), that are seen from a front-end and back-end nic?

    Is the front end nic on a domain that is also connected to the internet (even if that IP is per say not exposed)?

    Better to have your "app" machines dual homed and your SQL only homed to your internal back end nic address. Then use service / machine level accounts.

    The thing to take into account though is, be careful if you are running services like IIS, under an account that has super access to your data, but is also visible from the front end (internet) or anywhere else.

    Cheers

    • Marked as answer by jori5 Monday, September 21, 2015 7:24 AM
    Friday, September 4, 2015 1:57 AM
  • Thanks Anokneemous for your replay,

    "I am not sure what you mean by not allow access to back-end servers using front-end service accounts."
    By this I mean to not allow front-end guys to connect to SQL server and execute procedures or change data.

    We have only homed SQL server, and not sure about app servers, I hope they are dual-homed. Anyway

    We are looking at the solution to use only domain service accounts, and grant only execute permissions to the procedures on SQL servers database only. Is that correct way for best practice?

    Also are there any problems for implementing Domain service accounts on front-end side? Should IIS run under particular service account? Or it is as simple as on SQL (create login, db user and grant execute permissions)?

    Thank you

    Sunday, September 6, 2015 6:54 AM
  • Any update? Thanks

    Wednesday, September 9, 2015 7:41 AM
  • Hi jori5,

    ->We are looking at the solution to use only domain service accounts, and grant only execute permissions to the procedures on SQL servers database only. Is that correct way for best practice?

    That's great.

    ->Should IIS run under particular service account?

    Yes, it could. You could reference below link.
    http://www.iis.net/learn/manage/configuring-security/application-pool-identities

    If you encounter any problems during the operation, you could create a new thread on iis forum.
    http://forums.iis.net/

    Best Regards,
    Li Wang

    Friday, September 18, 2015 2:50 AM