locked
Web to SQL Server Connection RRS feed

  • Question

  • User-244455486 posted

    Hi,

    I have 2 servers :

    1. Web Server (WEB)

    2. SQL Server (SQL)

    There is a database called DB1 in SQL which its local account SQL\APP1 has db_owner rights. Can I configure WEB such that I connect to SQL via SQL\APP1 ?

    Is there some kind of impersonation or delegation ?

     

    Please advise.  Thanks !

    Wednesday, August 14, 2013 4:09 AM

Answers

  • User-1454326058 posted

    Hi limssd,

    Thanks for your post!

    According to your description, I would like to know some questions as follows:

    1. Whether the Website and SQL Server are in different Server?
    2. The account “SQL\APP1” is SQL account or Windows account?
    3. What’s version of your SQL Server?

    If the Website and SQL Server are in different Server, we should enable remote connections in SQL Server, more information, please refer to: http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

    If the “SQL\APP1” is a Windows account, I suggest that you should create a SQL account for your website, then configure the database connection string in web.config file, such as:

    <add name="TestIntDbConnection" providerName="System.Data.SqlClient" connectionString="Data SourceData Source=Server IP,port;Network Library=DBMSSOCN;Initial Catalog=DataBaseName;User ID=DBUserName;Password=DBPassword" />

    In conclusion, you don't need to configure the “SQL\APP1” in the Web Server, only need configure the account in SQL DataBase.

    Thanks

    Best Regards

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 15, 2013 4:58 AM
  • User1815411410 posted

    hi limssd,

    The issue can be easily resolved by creating a separate SQL Server Database User for your database. Next, you just have to alter your connection string on the web.config:

    <add name="connString" connectionString="Data Source=our_SQL_SERVER_ID;uid=your_DB_username;pwd=your_DB_password;Initial Catalog=your_DB_name"></add>

    Please mark this response as an "answer" if it helps you. Thanks heaps! 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 19, 2013 12:42 AM

All replies

  • User-1454326058 posted

    Hi limssd,

    Thanks for your post!

    According to your description, I would like to know some questions as follows:

    1. Whether the Website and SQL Server are in different Server?
    2. The account “SQL\APP1” is SQL account or Windows account?
    3. What’s version of your SQL Server?

    If the Website and SQL Server are in different Server, we should enable remote connections in SQL Server, more information, please refer to: http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

    If the “SQL\APP1” is a Windows account, I suggest that you should create a SQL account for your website, then configure the database connection string in web.config file, such as:

    <add name="TestIntDbConnection" providerName="System.Data.SqlClient" connectionString="Data SourceData Source=Server IP,port;Network Library=DBMSSOCN;Initial Catalog=DataBaseName;User ID=DBUserName;Password=DBPassword" />

    In conclusion, you don't need to configure the “SQL\APP1” in the Web Server, only need configure the account in SQL DataBase.

    Thanks

    Best Regards

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 15, 2013 4:58 AM
  • User1815411410 posted

    hi limssd,

    The issue can be easily resolved by creating a separate SQL Server Database User for your database. Next, you just have to alter your connection string on the web.config:

    <add name="connString" connectionString="Data Source=our_SQL_SERVER_ID;uid=your_DB_username;pwd=your_DB_password;Initial Catalog=your_DB_name"></add>

    Please mark this response as an "answer" if it helps you. Thanks heaps! 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 19, 2013 12:42 AM
  • User-244455486 posted

    Hi,

    Thanks for your reply.

    1. Whether the Website and SQL Server are in different Server? Yes, they are in different server.
    2. The account “SQL\APP1” is SQL account or Windows account? SQL\APP1 is a local windows account in SQL and it has db owner rights to the database DB1 in SQL.
    3. What’s version of your SQL Server? SQL Server 2008R2

    I've read http://msdn.microsoft.com/en-us/library/xh507fc5(v=vs.100).aspx. Can this be achieved by suppling the windows ID SQL\APP1 & password in Website config file and the web will connect to the SQL Server ?

    Wednesday, September 4, 2013 11:02 PM
  • User-1454326058 posted

    Hi limssd,

    As far as I know, the website authentication and SQL Server authentication are independent. If your website and SQL Server are in different Server, you should enable remote connections in SQL Server. (About how to enable remote connections in SQL Server, please refer to my previous reply).

    Here is a reference below that may benefit you:

    #How To  Set the SQL Server Network Library in an ADO Connection String

    http://support.microsoft.com/kb/238949

    Thanks

    Best Regards

    Thursday, September 5, 2013 9:38 PM
  • User-244455486 posted

    Hi,

    Let me refresh my config:

    I have 2 servers :

    1. Web Server (WEB)

    2. SQL Server (SQL)

    WEB is in DMZ whereas SQL is under a windows domain. There is a database called DB1 in SQL which its local account SQL\APP1 has db_owner rights. There is also a local windows ID in WEB called WEB\APP1. Both SQL\APP1 & WEB\APP1 have the same password.

    I have confirmed that by setting the application pool using intergrated authentication in WEB, it can connect to SQL via the ID APP1.

    Is there some kind of impersonation or delegation ? Please advise.

    Wednesday, October 16, 2013 3:30 AM