binding SQL server to localhost?

Greetings all,
I am a network security professional rather than a MS SQL admin, so I
apologize in advance if this is a bit of a basic question for this
list. I know an admin setting up a SQL server that will only be
accesible by a webserver running on the same host (not happy about
running private vs publicly avaialable services on the same host , but
it's what we've got). As such, I'd like to recommend to him that the
SQL server only listen on the localhost ip, 127.0.0.1, thereby making
it inaccesible to the outside world. I looked around the MS
knowledgebase but couldn't find a clear document stating how to do
this. Is it even possible? Is there a better option for this
configuration?
Thanks,
BrianYou propably need to firewall the server then.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
<tinbox@.nyct.net> schrieb im Newsbeitrag
news:1114053613.247153.87700@.o13g2000cwo.googlegroups.com...
> Greetings all,
> I am a network security professional rather than a MS SQL admin, so I
> apologize in advance if this is a bit of a basic question for this
> list. I know an admin setting up a SQL server that will only be
> accesible by a webserver running on the same host (not happy about
> running private vs publicly avaialable services on the same host , but
> it's what we've got). As such, I'd like to recommend to him that the
> SQL server only listen on the localhost ip, 127.0.0.1, thereby making
> it inaccesible to the outside world. I looked around the MS
> knowledgebase but couldn't find a clear document stating how to do
> this. Is it even possible? Is there a better option for this
> configuration?
> Thanks,
> Brian
>

binding SQL server to localhost?

Greetings all,
I am a network security professional rather than a MS SQL admin, so I
apologize in advance if this is a bit of a basic question for this
list. I know an admin setting up a SQL server that will only be
accesible by a webserver running on the same host (not happy about
running private vs publicly avaialable services on the same host , but
it's what we've got). As such, I'd like to recommend to him that the
SQL server only listen on the localhost ip, 127.0.0.1, thereby making
it inaccesible to the outside world. I looked around the MS
knowledgebase but couldn't find a clear document stating how to do
this. Is it even possible? Is there a better option for this
configuration?
Thanks,
Brian
You propably need to firewall the server then.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
<tinbox@.nyct.net> schrieb im Newsbeitrag
news:1114053613.247153.87700@.o13g2000cwo.googlegro ups.com...
> Greetings all,
> I am a network security professional rather than a MS SQL admin, so I
> apologize in advance if this is a bit of a basic question for this
> list. I know an admin setting up a SQL server that will only be
> accesible by a webserver running on the same host (not happy about
> running private vs publicly avaialable services on the same host , but
> it's what we've got). As such, I'd like to recommend to him that the
> SQL server only listen on the localhost ip, 127.0.0.1, thereby making
> it inaccesible to the outside world. I looked around the MS
> knowledgebase but couldn't find a clear document stating how to do
> this. Is it even possible? Is there a better option for this
> configuration?
> Thanks,
> Brian
>

binding SQL server to localhost?

Greetings all,
I am a network security professional rather than a MS SQL admin, so I
apologize in advance if this is a bit of a basic question for this
list. I know an admin setting up a SQL server that will only be
accesible by a webserver running on the same host (not happy about
running private vs publicly avaialable services on the same host , but
it's what we've got). As such, I'd like to recommend to him that the
SQL server only listen on the localhost ip, 127.0.0.1, thereby making
it inaccesible to the outside world. I looked around the MS
knowledgebase but couldn't find a clear document stating how to do
this. Is it even possible? Is there a better option for this
configuration?
Thanks,
BrianYou propably need to firewall the server then.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
<tinbox@.nyct.net> schrieb im Newsbeitrag
news:1114053613.247153.87700@.o13g2000cwo.googlegroups.com...
> Greetings all,
> I am a network security professional rather than a MS SQL admin, so I
> apologize in advance if this is a bit of a basic question for this
> list. I know an admin setting up a SQL server that will only be
> accesible by a webserver running on the same host (not happy about
> running private vs publicly avaialable services on the same host , but
> it's what we've got). As such, I'd like to recommend to him that the
> SQL server only listen on the localhost ip, 127.0.0.1, thereby making
> it inaccesible to the outside world. I looked around the MS
> knowledgebase but couldn't find a clear document stating how to do
> this. Is it even possible? Is there a better option for this
> configuration?
> Thanks,
> Brian
>sql

binding SQL server to localhost?

Greetings all,

I am a network security professional rather than a MS SQL admin, so I
apologize in advance if this is a bit of a basic question for this
list. I also cross-posted this to microsoft.public.sqlserver.server,
so sorry if anyone's read it already.

I know an admin setting up a SQL server that will only be
accesible by a webserver running on the same host (not happy about
running private vs publicly avaialable services on the same host , but
it's what we've got). As such, I'd like to recommend to him that the
SQL server only listen on the localhost ip, 127.0.0.1, thereby making
it inaccesible to the outside world. I looked around the MS
knowledgebase but couldn't find a clear document stating how to do
this. Is it even possible? Is there a better option for this
configuration?

It's been suggested that firewalling is the only option, but I'd really
like to do *both* (firewall & bind to localhost). The firewall in this
case will have to be host-based instead (software) instead of hardware
for non-technical reasons, so additionally if anyone recommends a
software firewall they use for this purpose I'd appreciate it. My firs
impulse is to recommend Tiny, but I've never used a software firewall
for an MS SQL/Web server before.

Thanks,
BrianOne option is to disable network access completely, and use only shared
memory for access to MSSQL (this is how MSDE operates by default since
SP3), so only applications running on the same machine will be able to
access it. Although someone could still attack MSSQL by compromising
the web server, or using SQL injection.

MSSQL itself doesn't provide any way to accept connections from
specified hosts - you would normally use the operating system's IP
filtering functions to do that.

Simon|||There isn't a way to have sql server listen on a specific ip. Sorry.

If you haven't already seen this, take a look at the Network
Configuration dialog on the general tab of the server's properties.
Two protocols are enabled by default. TCPIP is one of them, and you
can change the port and set it to ignore discovery broadcasts. That
would make the server invisible to anyone looking for it. However,
anyone who portscans the server would notice whatever port you put it
on and (I'd assume) be able to figure out that it's a SQL Server. So,
you do still need to firewall it.

The other protocol is called Named Pipes. That's basically using
memory to communicate. If the application you're developing supports
it, I'd suggest using this and turning off TCPIP support altogether.
This is probably faster anyway.

Chris

BI Portal connected to analysis services 2005

Anyone has connected the bi portal with analisys services 2005?

I have a security problem.

Thanks

Are you trying to troubleshoot connectivity to Analysis Services?

Is this HTTP or TCP connection?

See if this troubleshooting guide is going to help you: http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

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

|||

I had to do this a couple of days ago.

The only way I could get it to work was if I went into the advanced options on the Data Source and specified the full connection string myself, making sure to specify MSOLAP.3 as the provider. When I just chose the options from the standard connection dialog, I could not get it to work, however I was running the BI Portal for a demonstration from a virtual server so their could have been other complicating factors involved.

Note: because BI Portal uses OWC you will need to have the OLEDB provider for AS 2005 installed on all your client machines.

Hope this helps.

|||

I did what you said, i connect with the as2005 when i test the connection all work fine, i make the datasource but the webcomponents don't show the pivot table... it gives me an error and show nothing...

i don`t know what i can do... (i have the owc11 installed)

|||

Well, if the datasource tests OK then that is one hurdle we are over.

What is the exact error you are getting?

It may be something to do with the security in IE. As you may know OWC is an ActiveX control that runs on the browser and it needs to have higher than normal security rights. When you are connected to the BI Portal do you see "Trusted Zone" down in the bottom right hand corner? If so you might want to double check the rights for the Trusted zone and make sure that none of the ActiveX related permissions are disabled.

|||

When i am creating the dataosource i get this error:

The object doesn't accept this property or method.

The connection string i get is:

"Provider=MSOLAP.3;Cache Authentication=False;Persist Security Info=False;User ID=administrador;Initial Catalog=OnAlert2;Data Source=w2k3sql2005;Impersonation Level=Impersonate;Location=w2k3sql2005;Mode=ReadWrite;Protection Level=Pkt Privacy;Auto Synch Period=20000;Default Isolation Mode=0;Default MDX Visual Mode=0;MDX Compatibility=0;MDX Unique Name Style=0;Non Empty Threshold=0;SQLQueryMode=Calculated;Safety Options=1;Secured Cell Value=0;SQL Compatibility=0;Compression Level=0;Real Time Olap=False;Packet Size=4096;Initial Catalog=OnAlert2"

I am creating the connection using Analysis Services 9.0.

The pivot table doesn't load the cube i get the error, the pivot table says:

There is no details, the data provider doesn't provide more information.

Then i click in more information: Error = 0x80004005

|||I open excel to test, and i can connect to the cube.. so i don't have connections problems, the problem is with the owc..|||

I'm a bit confused, I thought you said earlier that you created and tested the connection OK, now you say that you are getting an error when you create the data source.

Why don't we try just setting the absolute minimum settings in the connection string and see if we can get a connection created without an error. Try a connection string like the following:

Provider=MSOLAP.3;Initial Catalog=OnAlert2;Data Source=w2k3sql2005;

|||

ok, how i set this connection string, now i am clicking in advanced then in create i put the name of the server etc, and it generates the connection string i can't modify it...

|||

Sorry, I don't know. I cannot enter a connection string directly into the advanced box, but if I connect first, or if I click Advanced and then browse and select a .oqy file, I can then edit the connection string.

I have not used BI Portal extensively, maybe you have an issue with your installation.

The main reason I suggested that you try a simpler connection string was the the sample you provided had the Initial Catalog setting listed twice and you had a User Id and no Password set, even though User Id only works with HTTP connections.

You could try creating an OWC page outside of BI Portal just to test the the OWC components are working. The easiest way to do this is to setup a pivot table in Excel and then choose File -> Save As Web Page and click on the add interactivity option.

This will setup a stand alone html page with an embedded pivot table, if it is running from a local drive, it will be running in the My Computer Zone, so it will probably have a different security profile than when you are running from the BI Portal, but it should atleast show us if the OWC components are working.

|||

I did what you say, i save the excel as a web page, and the owc give me an error:

"Can't process consult"

"The following system error ocurred: . "

I have the last owc...

|||

Those error messages don't really look like anything I have come across before.

If you can connect via Excel, that means your OLEDB provider is installed and working correctly.

If we cannot setup OWC in a simple html page on the local PC (I'm assuming that you save the file to a local drive, files running from network drives may have reduced priviledges) then there is little chance of it working from BI Portal.

Are you using at least IE 5?

In Internet Explorer, under Tools - Internet Options, there is an Advanced tab and under the security section there is a setting for "Enable Intergrated Windows Authentication" - Is this option ticked?

|||

I don't know what to do... perhaps i have to enable the http access?

The owc connect to the as2005 via http?

I really don't understand what is happening, i can connect with the cube via excel but when i am using the owc i have errors....

|||I have Internet explorer 6, i have the option you say enable...|||

I don't think http access will change much.

The fact that Excel works, proves that

You can authenticate to the AS server using your Windows credentials

BI Portal connected to analysis services 2005

Anyone has connected the bi portal with analisys services 2005?

I have a security problem.

Thanks

Are you trying to troubleshoot connectivity to Analysis Services?

Is this HTTP or TCP connection?

See if this troubleshooting guide is going to help you: http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

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

|||

I had to do this a couple of days ago.

The only way I could get it to work was if I went into the advanced options on the Data Source and specified the full connection string myself, making sure to specify MSOLAP.3 as the provider. When I just chose the options from the standard connection dialog, I could not get it to work, however I was running the BI Portal for a demonstration from a virtual server so their could have been other complicating factors involved.

Note: because BI Portal uses OWC you will need to have the OLEDB provider for AS 2005 installed on all your client machines.

Hope this helps.

|||

I did what you said, i connect with the as2005 when i test the connection all work fine, i make the datasource but the webcomponents don't show the pivot table... it gives me an error and show nothing...

i don`t know what i can do... (i have the owc11 installed)

|||

Well, if the datasource tests OK then that is one hurdle we are over.

What is the exact error you are getting?

It may be something to do with the security in IE. As you may know OWC is an ActiveX control that runs on the browser and it needs to have higher than normal security rights. When you are connected to the BI Portal do you see "Trusted Zone" down in the bottom right hand corner? If so you might want to double check the rights for the Trusted zone and make sure that none of the ActiveX related permissions are disabled.

|||

When i am creating the dataosource i get this error:

The object doesn't accept this property or method.

The connection string i get is:

"Provider=MSOLAP.3;Cache Authentication=False;Persist Security Info=False;User ID=administrador;Initial Catalog=OnAlert2;Data Source=w2k3sql2005;Impersonation Level=Impersonate;Location=w2k3sql2005;Mode=ReadWrite;Protection Level=Pkt Privacy;Auto Synch Period=20000;Default Isolation Mode=0;Default MDX Visual Mode=0;MDX Compatibility=0;MDX Unique Name Style=0;Non Empty Threshold=0;SQLQueryMode=Calculated;Safety Options=1;Secured Cell Value=0;SQL Compatibility=0;Compression Level=0;Real Time Olap=False;Packet Size=4096;Initial Catalog=OnAlert2"

I am creating the connection using Analysis Services 9.0.

The pivot table doesn't load the cube i get the error, the pivot table says:

There is no details, the data provider doesn't provide more information.

Then i click in more information: Error = 0x80004005

|||I open excel to test, and i can connect to the cube.. so i don't have connections problems, the problem is with the owc..|||

I'm a bit confused, I thought you said earlier that you created and tested the connection OK, now you say that you are getting an error when you create the data source.

Why don't we try just setting the absolute minimum settings in the connection string and see if we can get a connection created without an error. Try a connection string like the following:

Provider=MSOLAP.3;Initial Catalog=OnAlert2;Data Source=w2k3sql2005;

|||

ok, how i set this connection string, now i am clicking in advanced then in create i put the name of the server etc, and it generates the connection string i can't modify it...

|||

Sorry, I don't know. I cannot enter a connection string directly into the advanced box, but if I connect first, or if I click Advanced and then browse and select a .oqy file, I can then edit the connection string.

I have not used BI Portal extensively, maybe you have an issue with your installation.

The main reason I suggested that you try a simpler connection string was the the sample you provided had the Initial Catalog setting listed twice and you had a User Id and no Password set, even though User Id only works with HTTP connections.

You could try creating an OWC page outside of BI Portal just to test the the OWC components are working. The easiest way to do this is to setup a pivot table in Excel and then choose File -> Save As Web Page and click on the add interactivity option.

This will setup a stand alone html page with an embedded pivot table, if it is running from a local drive, it will be running in the My Computer Zone, so it will probably have a different security profile than when you are running from the BI Portal, but it should atleast show us if the OWC components are working.

|||

I did what you say, i save the excel as a web page, and the owc give me an error:

"Can't process consult"

"The following system error ocurred: . "

I have the last owc...

|||

Those error messages don't really look like anything I have come across before.

If you can connect via Excel, that means your OLEDB provider is installed and working correctly.

If we cannot setup OWC in a simple html page on the local PC (I'm assuming that you save the file to a local drive, files running from network drives may have reduced priviledges) then there is little chance of it working from BI Portal.

Are you using at least IE 5?

In Internet Explorer, under Tools - Internet Options, there is an Advanced tab and under the security section there is a setting for "Enable Intergrated Windows Authentication" - Is this option ticked?

|||

I don't know what to do... perhaps i have to enable the http access?

The owc connect to the as2005 via http?

I really don't understand what is happening, i can connect with the cube via excel but when i am using the owc i have errors....

|||I have Internet explorer 6, i have the option you say enable...|||

I don't think http access will change much.

The fact that Excel works, proves that

You can authenticate to the AS server using your Windows credentials

Best way to create a sql server role

I have an application that connects to SQL using SQL security. I would like
to give this user the permission to select data from all user tables and
execute all stored procedures.
I have accomplished this by creating a role and manually assigning the
"execute" permission to every stored proc to this role. Then I added the use
r
to that role. I have also assigned the user the db_datawriter and
db_datareader permissions.
My question is this - what happens when a new stored proc is added to the
system? Do I have to manually give that role execute rights to the new proc?
Is there a better way to accomplish this. To sumarize the requirements, the
user should be able to execute all stored procs and select data from any
table. Thanks!
- JohnnyHi
I hope you have already denied EXECUTE permission to public role.
You can generate dyamic script to grant an execute permissions and when a
new SP is added just reran the script and it will added to the role .
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:0346DA03-0106-47A7-937B-CFA32A5E345C@.microsoft.com...
>I have an application that connects to SQL using SQL security. I would like
> to give this user the permission to select data from all user tables and
> execute all stored procedures.
> I have accomplished this by creating a role and manually assigning the
> "execute" permission to every stored proc to this role. Then I added the
> user
> to that role. I have also assigned the user the db_datawriter and
> db_datareader permissions.
> My question is this - what happens when a new stored proc is added to the
> system? Do I have to manually give that role execute rights to the new
> proc?
> Is there a better way to accomplish this. To sumarize the requirements,
> the
> user should be able to execute all stored procs and select data from any
> table. Thanks!
> - Johnny|||Below is a script like the one Uri mentioned. Just change 'SpExecuteRule'
to your role(s).
--grant
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:0346DA03-0106-47A7-937B-CFA32A5E345C@.microsoft.com...
>I have an application that connects to SQL using SQL security. I would like
> to give this user the permission to select data from all user tables and
> execute all stored procedures.
> I have accomplished this by creating a role and manually assigning the
> "execute" permission to every stored proc to this role. Then I added the
> user
> to that role. I have also assigned the user the db_datawriter and
> db_datareader permissions.
> My question is this - what happens when a new stored proc is added to the
> system? Do I have to manually give that role execute rights to the new
> proc?
> Is there a better way to accomplish this. To sumarize the requirements,
> the
> user should be able to execute all stored procs and select data from any
> table. Thanks!
> - Johnny