How can I consume webservices on SQL 2005

Hi,
I'd like know if can I build ( and how can do ) a store procedore on MS
SQL 2005 to access a remote webservices, process it and return the result to
my client ?
Thanks,
Solli M. Honório
Hello Solli,

> I'd like know if can I build ( and how can do ) a store procedore on
> MS SQL 2005 to access a remote webservices, process it and return the
> result to my client ?
Yes.
The real trick is that you need to add build step that generates a static
proxy class. Do that by adding this as a Post-build Event Step in your SqlClr
project:
"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force
"$(TargetPath)"
Note that you cannot use Visual Studio to deploy all of the needed assemblies.
You'll need to fire up SSMS or like and issue these commands:
use ...whatever database you like...
go
create assembly ...what you want to call your assembly...
from ...where the DLL is on your disk...
with permission_set = external_access
go
create assembly [...whatever your project name is... .XmlSerializers]
from '...where the DLL is on your disk...\SqlServerProject1.XmlSerializers.dll'
go
create procedure ...whatever you want to call the procedure...(...list of
parameters, if any...)
as external name Assembly Name.[fully qualified class name]. ...name of method...
go
exec ...whatever you called the procedure...
go
The guts of my stored procedure looks like:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void WebMath(SqlInt32 x,SqlInt32 y)
{
SqlMetaData[] cols = new SqlMetaData[1];
cols[0] = new SqlMetaData("Result", SqlDbType.Int);
SqlDataRecord rec = new SqlDataRecord(cols);
using (SqlServerProject1.ws1proxy.ws1 proxy = new SqlServerProject1.ws1proxy.ws1())
{
rec.SetInt32(0, proxy.AddTwo(x.Value, y.Value));
SqlContext.Pipe.Send(rec);
}
}
The Web Service in question looks like:
public int AddTwo(int x,int y) {
return(x+y);
}
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

How can I consume webservices on SQL 2005

Hi,
I'd like know if can I build ( and how can do ) a store procedore on MS
SQL 2005 to access a remote webservices, process it and return the result to
my client ?
Thanks,
Solli M. HonórioHello Solli,

> I'd like know if can I build ( and how can do ) a store procedore on
> MS SQL 2005 to access a remote webservices, process it and return the
> result to my client ?
Yes.
The real trick is that you need to add build step that generates a static
proxy class. Do that by adding this as a Post-build Event Step in your SqlCl
r
project:
"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force
"$(TargetPath)"
Note that you cannot use Visual Studio to deploy all of the needed assemblie
s.
You'll need to fire up SSMS or like and issue these commands:
use ...whatever database you like...
go
create assembly ...what you want to call your assembly...
from ...where the DLL is on your disk...
with permission_set = external_access
go
create assembly [...whatever your project name is... .XmlSerializers]
from '...where the DLL is on your disk...\SqlServerProject1.XmlSerializers.d
ll'
go
create procedure ...whatever you want to call the procedure...(...list of
parameters, if any...)
as external name Assembly Name.[fully qualified class name]. ...name of meth
od...
go
exec ...whatever you called the procedure...
go
The guts of my stored procedure looks like:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void WebMath(SqlInt32 x,SqlInt32 y)
{
SqlMetaData[] cols = new SqlMetaData[1];
cols[0] = new SqlMetaData("Result", SqlDbType.Int);
SqlDataRecord rec = new SqlDataRecord(cols);
using (SqlServerProject1.ws1proxy.ws1 proxy = new SqlServerProject1.ws1proxy
.ws1())
{
rec.SetInt32(0, proxy.AddTwo(x.Value, y.Value));
SqlContext.Pipe.Send(rec);
}
}
The Web Service in question looks like:
public int AddTwo(int x,int y) {
return(x+y);
}
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/sql

How can I connect to a remote sql server using windows authentication?

It is simple

1- I open the Sql Server 2005 Management Studio

2- I select Windows Authentication from the drop down.

3- I cannot write the user name and password, it chooses the default once, the one I am logged in with!

But I am in a virtual machine outside the domain controller, I can access shares on machines that are on the domain controller, thanks to the file sharing of windows, but I cannot login to sql server, thanks to a meaningless restriction on that dialog :-)

Now, how can I still use the Windows Authentication and login, how can I avoid the sql server authentication?

If you are outside domain, then you should use SQL Authentication.|||

Ok, then why the text boxes for the user name and password are still there if I choose the Windows authentication, and the text boxes have the user name already filled in, and all are disabled.

What is the point of having those there? In my case, I was trying to find a way to enable them from the settings; I guess just a false hope.

And why cannot I use the windows authentication? NTFS does allow me to do it and access the file system from outside the domain using windows authentication against the domain, what does make sql server more special?

|||

The reason for the textbox is just to let you know which Windows account is being used to connect to SQL Server using Windows authentication. To access SQL Server using SQL authentication, click the Authentication drop-down to see the SQL Server Authentication option. You'll see the User name and Password textboxes enabled.

If you want to use Windows authentication, the easiest way is to join your SQL Server to your domain.

|||

Thank you for the help, but I know how to use the SQL Server authentication, and the SQL Server is the development server and it is on the domain.

My virtual machine is the development machine, it is a virtual machine and it cannot join the domain, it must stay as it is, the real machine is on the domain, but the virtual machine that I am trying to use is not.

From the virtual machine I can do lots of things, including accessing the file system and the intranet sites on the domain, using the domain authentication box, or cached credentials, but I cannot do that with the SQL Server.

|||

if you want an nt authentication

then you must promote your virtual machine to a domain controller

How can I administer SQL Database on remote Web Server

Hello Everybody

Please tell me which is the best method to adminster SQL Server
database in remote web server.

Eg. I am storing all the information of SQL database in my work place
and after that have to upload the modified table to remote web server.
Please tell me what is the easiest way or how I can achieve this
without purchasing third party applications.

With regards

Babu ThomasOn 29 Jul 2003 01:10:50 -0700 in comp.databases.ms-sqlserver,
babu_nir@.hotmail.com (Babu Thomas) wrote:

>Hello Everybody
>Please tell me which is the best method to adminster SQL Server
>database in remote web server.
>Eg. I am storing all the information of SQL database in my work place
>and after that have to upload the modified table to remote web server.
>Please tell me what is the easiest way or how I can achieve this
>without purchasing third party applications.

The easiest way would be to have port 1433 open on the web server and
use EM to manage it. If your web server is VPN enabled use that
otherwise if firewalled you may be able to opne up that port to allow
only your IP address in.

Note, it's not such a good idea to leave this port open to the rest of
the world, it *will* get hacked I promise you, there's no maybes on
this one, it *will* get hacked.

--
Ride Free (but you still have to pay for the petrol)

(replace sithlord with trevor for email)

How can connect SSAS w/o domain trusted connection?

I got error: An existing connection was forcibly closed by the remote host!!

string connstr = "Provider=MSOLAP.3;Data Source=amsserver;Password=;User ID=administrator;Initial Catalog=MIP2ASProject";

Client in XP, with AS9.0 provider installed, server is sqlserver 2005 in win2003 xp1.

Both machines are not under domain controller...

Moving to SQL Server Analysis Services forum.|||

Analysis Services does not support non-Windows authentication when connecting through TCP/IP.

You should be able to setup HTTP connectivity to SSAS.
See following:
http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx

And then use different type of authentication avaliable in IIS to connect to Analysis Server.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

How best to do remote site replication?

It has been suggested that we consider how to replicate our production
server to a remote site that does not have a dedicated connection to our
production environment. Any and all suggestions are appreciated.
TIA
Michael MacGregor
Database Architect
SalesDriver
Michael,
assuming there's no direct connection, this type of setup is usually done
through a VPN.
Here is a reference to help you:
http://support.microsoft.com/?id=321822.
Regards,
Paul Ibison
|||Additionally, I should mention that pull subscriptions would be most
suitable.
Regards,
Paul Ibison
|||Thanks Paul.
Totally new to replication and our IT guy is proposing some really weird and
wacky ideas on how to do this, one of which is low-level bit comparison, and
subsequent updating, of backup files to use to restore a database. Makes me
shake my head in wonder.
Michael MacGregor
Database Architect
SalesDriver
|||Michael,
if this is for disaster recovery you might want to consider log-shipping.
There have been a few threads recently (last 2 weeks) comparing this to
transactional replication which might be helpful, and it is a standard way
of maintaining a database copy. Using propriety solutions like
bit-comparisons would no doubt not be supported by ms, and presumably would
require a comparison of the full database backup. Also, log shipping will
just require that you transmit the changes which are in the log to the
remote site rather than full backups.
HTH,
Paul Ibison
|||To be honest, I'm not too sure what problem this is supposed to address. We
have an active/passive SQL Server cluster which has only been required once
in over three years. In addition there is a fairly standard backup/recovery
plan in effect with regular backups, both full and transactional, performed.
The IT guy has got the idea of having another SQL Server installed at a
remote site, to act as what I'm not sure, but wants to have it as close to
the production server as possible.
I wondered about log-shipping as well, but I'm not even sure that this idea
is going to fly anyway, but thought I'd do a little research of my own
before this issue gets raised on a more formal basis, at which point in time
I can address it better then.
Would you be able to provide me with the subject titles of the threads you
refer to?
Michael MacGregor
|||Michael,
the main thread was entitled "Disaster recovery - Use Replication or Log
shipping".
HTH,
Paul Ibison

How (un)secure is my remote SQL Server?

I've been provided with a server at a hosting company. The server is running W2K3 SP2 in its own workgroup (i.e., non-AD) configuration, but is not behind any type of hardware firewall; there is no VPN in place, either. I connect to the server via RDP using an extremely long and complex password. I'm using the newest version of the RDP client. The article "Hacking RDP" and the ensuing reader comments (http://mcpmag.com/columns/article.asp?EditorialsID=1699) indicate that using RDP in this fashion is relatively safe.

I installed SQL Server 2005 SP2 on this server. I set server authentication to 'SQL Server and Windows Authentication mode'. I created one obscure SQL Server login, using another extremely long and complex password. I also disabled the login for the 'sa' account.

Since installing SQL Server on this server, I've noticed thousands of Failure Audit events in the server's Application log:

Source: MSSQLSERVER

Description: Login failed for user X

where X equals 'administrator', 'root', 'server', 'database' 'sql', 'sa', etc.

These failure events occur almost non-stop, about a dozen per second, and come from a small pool of unknown IP addresses. The IP address seems to change every few hours. I'm guessing that someone is hoping that one of these names is an actual SQL Server login and is trying a brute-force attack to try to stumble upon a matching password. None of these logins are valid, but it's still disconcerting. Is this anything to be concerned about? I could have the hosting company block the IP addresses, but that seems like a losing battle.

Lastly, I used the Surface Area Configuration tool to allow local and remote connections, using TCP/IP only--so that I could begin interacting with this SQL Server from my PC, using both SQL Server Management Studio and my own Visual Studio code. For each method, I'm using the obscure SQL Server login that I created earlier--the one with the extremely long and complex password. How (un)secure is my traffic to/from this SQL Server? I don't believe that my credentials are encrypted, but I'm not sure how much of a risk this is nor do I know how else to more securely connect to SQL Server.

Given these circumstances, is there any way to make this resource more secure? Thanks!

Every hacker in the world (or so it seems) has automated tools that are always pinging port 1433.

You can increase your 'invisibility' by changing the IP port -preferrably to a high IP number, and make sure that port 1434 is closed on the windows firewall, and that the SQL Browser service is not running.

You are correct, that without a VPN, or SSL connection, you are sending the SQL password in clear text across the wire -in the context of your RDP connection.

While you have a small vulnerablity, it is relatively insignificant. Should you be concerned? If the database has sensitive data, protected data, or heads would roll if the data was hacked -get a VPN (or SSL) connection. Most hackers are seeking the 'low hanging fruit' of open and unprotected servers.

|||

Thanks. Please pardon my ignorance. How does blocking port 1434 on the Windows firewall help? Are there any negative side-effects of stopping the SQL Server Browser service?

>>You are correct, that without a VPN, or SSL connection, you are sending the SQL password in clear text across the wire -in the context of your RDP connection.

I think I was unclear in my original explanation. I'm using SQL Server Management Studio and Visual Studio-based applications from my PC to connect (via clear text) to the server's SQL Server over the Internet--not via the RDP connection. I mentioned RDP just in the context of how I administer the server, when necessary; RDP is not in use otherwise.

I'm not sure where to begin with a VPN; do you have any recommendations? The hosting company mentioned OpenVPN, but I'm not making much progress with that. I might make better progress via a SSL connection. Can you recommend any references on how to get started with that--in particular, regarding SQL Server traffic? Or any articles in general that apply to my kind of scenario? (unprotected server at a hosting company)

|||

If you are managing the SQL Server 'in the wild' (and not over the RDP) then you are really exposed and totally dependent upon the strength of your passwords and security model used in SQL Server.

Whatever solution you settle upon, it will have to be something that the hosting company is willing to deal with and support -since it has to be on their end and you probably cannot physically go there and set it up or mainain it.

If they suggested OpenVPN, I would start exploring that product. Personally, I have no experience with it.

Setting up SSL should be something that the host company should easily handle -but there is a cost for the certificate. (But not too onerous for the level of 'security' it provides.)

|||

Thanks again, Arnie. I forgot to mention that the SQL Server Browser service is already set to Disabled.

Your earlier comment got me thinking. I configured the Windows firewall to block all traffic other than port 3389 (RDP)--and that, with only the somewhat-static IP address of my PC. Of course, this had the immediate side-effect of preventing me from accessing SQL Server via SQL Server Management Studio on my PC. Next, I further configured the Windows firewall to allow port 1433 traffic--again, with just the IP address of my PC. SQL Server Management Studio was then able to connect to the server. That seems about as secure as I know how to make it for the time being. Now I just have to make sure that the IP address of my PC doesn't change!

I've opened another dialog with the hosting company. It's not clear to me how setting up a VPN will thereafter protect the server; is it that the server no longer responds to any other traffic other than that over the VPN port?

I don't mind purchasing an SSL certificate; just by itself, however, it's not clear to me how that will protect the server as a whole--like a VPN might. Wouldn't SSL result in just SQL Server traffic being encrypted?

|||

It may be time to bite the bullet and pay the extra for a static IP address (business expense and all...)

A VPN tunnel is somewhat similar to the RDP connection -with the extension that what is on the other end of the tunnel appears to be local to your network/computer. ALL internet traffic is encrypted between the endpoints. It is impossible for anyone to 'sniff' your packets and pick out data (or passwords).

For this usage, I think that VPN may be a better option that setting up SSL -unless your business needs SSL for its website.

|||

I agree that a VPN is the way to go. I've begun perusing the OpenVPN documentation, but it's a bit disjointed.

Can you recommend a different VPN?

I can't help but wonder whether Windows' RRAS might work; however, I have yet to find any helpful documentation about that feature.

Hosting an SQL database on remote share

Hi,
I would really appreciate help with this query.

I have an SQL 2000 server setup with 2 databases. My boss would like to setup another database but wants the actual database to reside on a different server share.

Is is possible for one database on an SQL server to be hosted on a remote share ?

appreicate any info
thanksIt can be done, but it is difficult, complex, and unsupported. I wouldn't go there, the risk is way too high.

-PatP|||Main point to think about is Performance and is there any reason why your boss is thinking in such a high-fly idea.