Interactive data access through firewall

Hi there!

I am facing this problem:

I have server A (SQL Server 2005) in LAN and server B (IIS 6) in DMZ.

Due to security policies, server A can initiate communications to B through a firewall; server B cannot initiate any communication to server A.

A web application on server B needs to interactively read/write data stored on server A.

I was reading something about repication, sql everywhere, service broker and something else.

Is there some integrated functionality in SQL Server 2005 that can help in this scenario, or should I develop some sort of sync application?

Thank you.

Bye!

Well, I would not suggest you to implement a sync mechanism on your own. Either use a local database which is replicated to the main server or open the firewall on the dedicated port and restrict the access to the IP of the web server and implement certain security rules like IPSec , using certificate based communication, encryption etc. to prevent any attack to your server.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thank you for your answer.

I don't know exactly why, but they don't want data to be stored on the published server, nor in any other server in DMZ.

Since I have not much experience in firewalling and network rules, I am trying to adapt my software solution to the existing environment.

I developed a semi-interactive communication app based on asynchronous webservices, where server A polls from server B for queries that should be executed, and returns the resultsets. It's much like Exchange's "Direct Push Emails".

I am just wondering if this is the best solution, or if I should simply pretend some ports be opened on the firewall...

Interactive data access through firewall

Hi there!

I am facing this problem:

I have server A (SQL Server 2005) in LAN and server B (IIS 6) in DMZ.

Due to security policies, server A can initiate communications to B through a firewall; server B cannot initiate any communication to server A.

A web application on server B needs to interactively read/write data stored on server A.

I was reading something about repication, sql everywhere, service broker and something else.

Is there some integrated functionality in SQL Server 2005 that can help in this scenario, or should I develop some sort of sync application?

Thank you.

Bye!

Well, I would not suggest you to implement a sync mechanism on your own. Either use a local database which is replicated to the main server or open the firewall on the dedicated port and restrict the access to the IP of the web server and implement certain security rules like IPSec , using certificate based communication, encryption etc. to prevent any attack to your server.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thank you for your answer.

I don't know exactly why, but they don't want data to be stored on the published server, nor in any other server in DMZ.

Since I have not much experience in firewalling and network rules, I am trying to adapt my software solution to the existing environment.

I developed a semi-interactive communication app based on asynchronous webservices, where server A polls from server B for queries that should be executed, and returns the resultsets. It's much like Exchange's "Direct Push Emails".

I am just wondering if this is the best solution, or if I should simply pretend some ports be opened on the firewall...

Inter Database Security

I have a stored procedure db1.dbo.sp1
this Stored Procedure grabs data from another database. Say db2.dbo.tbl1

If i call the qry1.
how does the security from db2 come in to play? if at all?

For example, If I am SQL Server Authenticated, for DB1 but not for DB2 will the SP fail. etc.If you have security to db1 but not db2, and you run it as yourself, it will fail.

HTH|||Tnx for the info. I picked up some additional tips and was able to confirm your suggestion on our sql servers over here.

Is there anyway to use NT Authentication in the first db. Then from within a stored procedure (in the 1st database), access the 2nd database using the sa account?

I.E. pass a sa login request to the 2nd database.

Integrating Hardware Security Module (HSM) for Cryptographic Key storage with SQL Server

Hi,

I have come to know from the SQL Server documentation that the Hardware Security Modules (HSM) can be used for Cryptographic Key storage within SQL Server to increase the level of protection of data at rest. We provide a HSM Solution which protects cryptographic keys and performs cryptographic operations onboard. The aforesaid fact about SQL Server and HSMs motivates us to test our HSM product with the SQL Server. Our HSM solution provides SDK for applications/servers to communicate with the HSM hardware. The SDK basically consists of two libraries:

-MS CAPI Interface (CSP Library)

-PKCS#11 Interface (cryptoki Library)

I have found this forum best for such kind of discussion. So could you guys on the list let me know:

- How the CSP or PKCS#11 library can be integrated with the SQL Server for HSM box to protect the cryptographic keys?

- Would I need to write a new Interface/Wrapper for this integration, If yes please help?

Thanks in advance.

Harsh.

hi harsh,

We cant do it in SQL Server 2005 as it dosn't support third party CSP. Infact i also want to store my Private key in HSM. I tried but there is no help available from SQL Server 2008 Documents regarding integration of SQL Server with HSM. I would also appraciate if there is some form of document available for Integration. Can anyone from Microsoft SQL Server team help ?

Regards,

Bansal.

|||

Wanni is correct: you cannot do this in SQL Server 2005. For discussions and suggestions for future SQL Server versions, you can visit the SQL Server Katmai Security forum.

Thanks

Laurentiu

Integrated Security...

Does anyone know how to impersonate a user and then use integrated security with SQL server? Every place I've looked so far only shows how to use integrated security through IIS. For some reason, everytime I impersonate a user account, SQL server identifies me as "NT AUTHORITY\ANONYMOUS LOGON" Is this by design? or am I doing something wrong?I don't think that the problem you are seeing is due to SQLServer. This is most likely due to your impersonation configuration in IIS. It looks like the impersonation you are doing is not allowed to go out on network (it is like going LogonUser with LOGON32_LOGON_NETWORK flag), that's why when you connect to SQLserver your identity is not the one you expect to be after impersonation. I recommend reading IIS/ASP.Net documentation on that.|||

Thanks for your quick response... but one small detail. I'm not using IIS or asp.net. I'm using the traditional "logonuser" API method. Thanks anyway. And, yes... I'm using the LOGON32_LOGON_NETWORK flag.

|||

Just to conclude: the problem stems from using the LOGON32_LOGON_NETWORK flag, as Ruslan pointed out.

Thanks
Laurentiu

Integrated Security with local SQL & IIS

I have SQL RS running on a Win 2K (dev only) machine with IIS on the same local machine. I am trying to access the reports from another computer, but am getting an error An error has occurred during report processing. (rsProcessingAborted) Get Online Help Cannot create a connection to data source '<Shared Data Source Name>'. (rsErrorOpeningConnection) Get Online Help Login failed for user '<DOMAINNAME>\Guest'.

IIS and the Datasource are setup for integrated security and the Datasource is aimed at the local SQL DB. Anonymous access is turned off in IIS and it prompts for the login info when trying to access it via the web. My understanding is that this should work without problems due to IIS & SQL being on the same machine, but I can't seem to get it to work. Is there a doc somewhere or anything that goes thru the settings so I can see what I'm missing? Or does anyone have any ideas?

Thanks.

Does it work if you access the reports locally? How about using stored windows credentials for hte data source instead of integrated security?|||

Yes, the reports work fine locally. I just can't access them from another machine. I need to use integrated security due to needing the windows login to lookup the correct info in the report and to know the type of user (admin, basic, etc).

Thanks for your help.

|||

Probably a kerberos configuration issue. Try forcing NTLM. See the workaround in http://support.microsoft.com/default.aspx?scid=kb;en-us;871179