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

Binding Render output to ReportViewer control?

I'm trying to take the output of the Render method and bind it to the ReportViewer control. First of all, is this possible? If so, what is the best way to render the report (format: XML,CSV, NULL, etc), and then how to I 'bind' the result to the control?

I'm proving some code to demonstrate how I'm rendering the report:
...
string format = "XML";
string devInfo = @."<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";
ReportExecutionService rs = new ReportExecutionService();
ExecutionInfo execInfo = new ExecutionInfo();
ExecutionHeader execHeader = new ExecutionHeader();
byte[] result= null;

rs.ExecutionHeaderValue = execHeader;
execInfo = rs.LoadReport(path, null);
string SessionId = rs.ExecutionHeaderValue.ExecutionID;
result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

Thanks, any help would be greatly appreciated.
Peter*bump*

Just trying to get a feel for what everyone else is using this feature/method for?

Thanks,
P

Binding last row in table to a label

Hi all. I have a label on my page and I want to bind it to a field in a table. The catch is that I want to bind it to the last row in the table. I think I can use the custom binding, but I don't know how to bind to the last row. Any Suggestions ?

p.s. The page is tied to an SqlDataSource that retrieves the data from the above table.

Thanks in advance.

Could you not change the datasource to only return the last row? Or did you want all the other rows as well?

What about

Label1.text = dt.Rows(dt.Rows.Count-1)("FieldName").ToString()

where dt is the datatable containing the data you are binding to.

|||

Thanks for the reply GavDraper,

but what is the type of dt, how can I use a sql server table in my code ? and what is the meaning of "FieldName" ?

|||

the type of dt is datatable. You would need to replace fieldname with the name of the field you want to pull data from. below is a very rough quick example although I'm unable to test this as im not at my development machine, you would also want to include some error handling

dim dt as new datatable()dim sqlCon as new sqlConnection(strCon)dim sqlAdp as new SqlDataAdapter()sqlAdp.SelectCommand.Connection = sqlCon()

sqlAdp.Selectcommand.CommandText ("SELECT * FROM tablename")sqlAdp.fill(dt)lbl1.Text = dt.Rows(dt.Rows.Count-1)("FieldName")

|||

I'll give it a try,

but is it possible to do it without the code behind ?

I have the label and I use databinding to bind the fields in the table using the sqldatasource, but somehow it gives me the first row in the table.

I use a stored procedure to select the data from the table and use parameters in that stored procedure. So is it possible to bind to these parameters declaratively so they would give me the fields in the last row ?

The stored procedure:

CREATE PROCEDURE dbo.createPage@.PageTitlenvarchar(300)OUTPUT, @.PageMetaDescnvarchar(300)OUTPUT,@.PageMetaWordsnvarchar(300)OUTPUT,@.PageDescnvarchar(300)OUTPUT,@.PageTemplateint OUTPUTASDECLARE @.pIdAS intSELECT @.pId =cast(SCOPE_IDENTITY()AS int)--selecting the id of the last row inserted--selecting the data from that rowSELECT PageTitle,PageMetaDesc,PageMetaWords,PageDesc,PageHTML, PageTemplate, PageSummaryHTMLFROM PagesWHERE PageId = @.pIdGO

|||

that stored procedure is returning the data in no particular order how can you be sure its always the last record you want? You could change the stored procedure to only return 1 record by using the TOP keyword but to make this accurate the data would have to be ordered to guarentee you will always get the correct row.

binding input parameters leads to SQL_ERROR

I am trying to read the results from a query like
select a, b
from mytbl
where col0 = ?
The first time I run SQLExecute, everything works fine, but when I call
SQLExecute a second time with a modified input parameter, it returns
SQL_ERROR. Below is the code I'm running with the error checking removed and
the names simplified.
SQLAllocHandle(SQL_HANDLE_STMT, hconn, &m_hstmt)) ;
SQLPrepare(m_hstmt, (UCHAR *)m_query.GetBuffer(), m_query.GetLength()));
SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0,
&where_param, 0, 0);
SQLBindCol(m_hstmt, 1, SQL_C_BIT, addr1, 0, 0)
SQLBindCol(m_hstmt, 2, SQL_C_BIT, addr2, 0, 0)
SQLExecute(m_hstmt) //works ok
SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
where_param = 10
SQLExecute(m_hstmt) //fails
SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
My goal is to run execute many times with a variety of parameters. I was
trying to have the odbc driver read from the address specified in
SQLBindParameter to set the value of the input parameter. I'm further
confused by the problem because when I try to retrieve error information by
calling SQLGetDiagRec, nothing is returned. Thanks for your help.
Scott
The problem goes away when I call SQLCloseCursor after completing the fetch.
The MSDN SQLBindParameter documentation at
http://msdn.microsoft.com/library/de...dparameter.asp
gives no indication that SQLCloseCursor should be needed.
I suppose that I am now just concerned that I am introducing extra
processing overhead by calling SQLCloseCursor.
Scott
"ScottD" wrote:

> I am trying to read the results from a query like
> select a, b
> from mytbl
> where col0 = ?
> The first time I run SQLExecute, everything works fine, but when I call
> SQLExecute a second time with a modified input parameter, it returns
> SQL_ERROR. Below is the code I'm running with the error checking removed and
> the names simplified.
> SQLAllocHandle(SQL_HANDLE_STMT, hconn, &m_hstmt)) ;
> SQLPrepare(m_hstmt, (UCHAR *)m_query.GetBuffer(), m_query.GetLength()));
> SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0,
> &where_param, 0, 0);
> SQLBindCol(m_hstmt, 1, SQL_C_BIT, addr1, 0, 0)
> SQLBindCol(m_hstmt, 2, SQL_C_BIT, addr2, 0, 0)
> SQLExecute(m_hstmt) //works ok
> SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
> where_param = 10
> SQLExecute(m_hstmt) //fails
> SQLFetchScroll(m_hstmt, SQL_FETCH_NEXT, 0)
>
> My goal is to run execute many times with a variety of parameters. I was
> trying to have the odbc driver read from the address specified in
> SQLBindParameter to set the value of the input parameter. I'm further
> confused by the problem because when I try to retrieve error information by
> calling SQLGetDiagRec, nothing is returned. Thanks for your help.
> Scott
|||Are you making sure that you've fetch all of the rows? Even if only
one row is returned by the query, you should call SQLFetchXXX() until
it returns SQL_NO_DATA. Otherwise the driver doesn't necessarily know
that you've finished fetching rows on the current resultset/cursor.
Alternatively, you can use SQLMoreResults() to flush the remaining data
on the wire and position on the next resultset (if there is one,
otherwise it cleans up the connection).
SQLCloseCursor shouldn't add too much overhead. I'm assuming you're
using the default (firehose) cursor. If so, SQLCloseCursor just makes
sure that all of the data from the previous statement has been
consumed, and the connection is ready for the next statement.
Brannon

Binding Gridview from two different tables

Hi all,

The Scenario:

Database1:Table1(callingPartyNumber,originalCalledPartyNumber, finalCalledPartyNumber, DateTimeConnect, DateTimeDisconnect, Duration)

Database2:Table2(Name,Number)

Output in Gridview:

callingPartyNumber

NameoriginalCalledPartyNumberfinalcalledPartyNumberdateTimeConnecteddateTimeDisconnectedDuration (HH:MM:SS)

I bind gridview programatically using DataTable and stored procedures. The data comes from a table (Table1) in a database (Database1) on SQL Server. The gridview displays fields callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber, DateTimeConnect, DateTimeDisconnect and Duration in this order. All the columns in this gridview are databound columns.

I have another table (Table2) in a seperate SQL Server database (Database2) but on the same server which maps the callingPartNumber value with the name attached with that number. Note that the field names in Table2 are different from the field names in Table1. Is it possible to display the Name field also in the gridview after the first field callingPartyNumber and then the other fields.

Its like data coming from two tables into the gridview.

Thanks

I would suggest you to use a View in GridView by joining this tables.

CREATE VIEW dbo.Callers AS

SELECT dbo.Database1.Table1.* dbo.Database2.Table2.* FROM dbo.Database1.Table1 RIGHT OUTER JOIN dbo.Database2.Table2 WHERE dbo.Database1.Table1.CallingPartyNumber = dbo.Database2.Table2.Number

|||

SELECT callingPartyNumber,Name,originalCalledPartyNumber, finalCalledPartyNumber, DateTimeConnect, DateTimeDisconnect, Duration

FROM database1.dbo.Table1 t1

LEFT JOIN database2.dbo.Table2 t2 ON t1.callingPartyNumber=t2.Number

Change "LEFT JOIN" to "JOIN" if you only want records that have a name.

|||

Fabulous!!! Thank you so much. I guess I explained my problem nicely this time...eh...One question though, I didn't even get a chance to try your suggestion and the post was marked as answer within a few minutes of your posting it. I read somewhere, most probably in a post from a moderator, that they give us a chance (usually 48 hrs) to mark the post as an answer.

|||

I am still trying to figure out this but thought would share. You might know what I am doing wrong. When I do the LEFT JOIN, it pulls all the names right, but the total count of records is always a bit off than when I don't do a LEFT JOIN. I read about all the joins and LEFT JOIN is what I am looking for, but I think I am going wrong somewhere. I also have a WHERE clause added to the query.

CREATE Procedure GetCDR_LikeTollFree ( @.theDurationint =null,--to find duration more than theDuration @.totalDurationbigint =null,--to find the total duration for the result set @.callingPartyNumbervarchar(255) =null, @.originalCalledPartyNumbervarchar(255) =null, @.finalCalledPartyNumbervarchar(255) =null, @.dateTimeConnectdatetime =null, @.dateTimeDisconnectdatetime =null )AS BEGIN SELECT callingPartyNumber,Name, originalCalledPartyNumber, finalCalledPartyNumber,dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 ,'01-01-1970 00:00:00')AS dateTimeConnect,dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600,'01-01-1970 00:00:00')AS dateTimeDisconnect, durationFROM db1.dbo.Calls t1LEFTJOIN db2.dbo.NumPlan t2ON t1.callingPartyNumber=t2.NumberWHERE (t1.callingPartyNumberLIKEISNULL(@.callingPartyNumber, t1.callingPartyNumber) +'%')AND (t1.originalCalledPartyNumberLIKEISNULL(@.originalCalledPartyNumber, t1.originalCalledPartyNumber) +'%')AND (t1.finalCalledPartyNumberLIKEISNULL(@.finalCalledPartyNumber, t1.finalCalledPartyNumber) +'%')AND (t1.duration >= @.theDuration)AND ((t1.datetimeConnect - 14400) >=ISNULL(convert(bigint,datediff(ss,'01-01-1970 00:00:00', @.dateTimeConnect)), t1.datetimeConnect))AND ((t1.dateTimeDisconnect - 14400) <=ISNULL(convert(bigint,datediff(ss,'01-01-1970 00:00:00', @.dateTimeDisconnect)), t1.dateTimeDisconnect))END

The query before inserting the LEFT JOIN was giving the correct number of records.

|||

Sorry about that...I am pretty sure the problem is at the table end. I have some bad data (duplicates) and thats creating a problem.

|||

Looking at your query, the only reason I can see that the record counts would be different is if in the NumPlan table, you have more than 1 name listed for a specific Number. If that is the case, then you will get one record back for each name.

As for your other question, moderators can also mark posts as answered (Submitters can always unmark them if need be). But it helps when people are scanning the forums looking for questions that haven't been answered, so I normally mark posts that I feel have a 95%+ chance of fully answering the question as answered. Unfortuantely, the moderator and MVP tags are a bit quirky and they come and go by themselves sometimes, so it's hard to tell who is a moderator/mvp and who isn't.

|||

Motley:

Looking at your query, the only reason I can see that the record counts would be different is if in the NumPlan table, you have more than 1 name listed for a specific Number. If that is the case, then you will get one record back for each name

You are absolutely right. I figured that out but it took some time. I keep trying myself and when I fall short of time, I post, and it has happened so many times that I figure out the solution to the problem after posting.

Motley:

As for your other question, moderators can also mark posts as answered (Submitters can always unmark them if need be). But it helps when people are scanning the forums looking for questions that haven't been answered, so I normally mark posts that I feel have a 95%+ chance of fully answering the question as answered. Unfortuantely, the moderator and MVP tags are a bit quirky and they come and go by themselves sometimes, so it's hard to tell who is a moderator/mvp and who isn't.

I was almost sure that you marked that post as answer as it wasTHEanswer. Don't worry about it. I only mentioned that as I read that moderators generally give us 48 hrs.

sql