Internet integration: sqlserver

Showing posts with label sqlserver. Show all posts

Monday, March 26, 2012

Invalid object name while reading data out of an SQL Database

Hi all,
I'm a complete newbie on ASP.Net.

I want to get some data out of a SQLserver Database running on my system with SQL Server 2005 Express. The name of the Database is 'tempdb' and the table is called "Members". the SQLServer runs as Local System with the Windows account.

When I try to open the site, I always get the same error:
Invalid object name 'Members'

I don't know what to do anymore. I read a post, where anybody set the rights for the owner, but my database is running with the Windows account.

Here is the Code of the page so far:

<%@. Page Language="VB" Debug="True" Strict="True" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SQlClient" %>
<script runat="server">
Sub Page_Load (ByVal Sender As Object, _
 ByVal E As EventArgs) Dim connStr As String
 connStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
 connStr += "database=tempdb;"
 connStr += "Truster_Connection=yes"
 Dim conn As New SQLConnection(connStr)
 conn.Open()
 Dim sql As String
 sql = "SELECT COUNT (*) FROM Members"
 Dim cmd As New SQLCommand(sql, conn)
 Dim ergebnis As String
 ergebnis = cmd.ExecuteScalar().toString()
 Dim t As String
 t = "Die Tabelle Members hat " & _
 ergebnis & " Zeilen. <br>" & _
 "Das Kommando lautet: " & _
 cmd.CommandText & "<br>" & _
 "Der Kommandotyp ist: " & _
 cmd.CommandType
 ausgabe.innerHTML = t
End Sub
</script>
<html><head><title>
Demo zu SQLCommand.ExecuteScalar
</title></head>
<body>
<h3>Demo zu SQLCommand.ExecuteScalar</h3>
<p runat="server" id="ausgabe" />
</body></html>

Thanks for your help an sorry for my english.

Greets
Flash_Prince

The problem is objects in tempdb is valid for limited time so your object the Member table does not exist. Microsoft provided the tempdb for SQL Server to be used to proccess complex queries and test queries so when you create a table if you did not drop it SQL Server will drop it at some point. Try the links below to download sample databases you can create and use with SQL Server 2005. The databases comes with tables but you can create your own tables so you can add the member table to any of the three database. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en

|||

The problem is, that i want to use the database without entering the path of the database file in the sourcecode. When I use the nordwind database, all the sql queries are working very well. But if I use the Nordwind Database, I only know to connect by OleDB.

Can you tell me how to use a database in the way decribed above with a sql connection?

I hope, this is described understandable, cause my english is not the best.

|||

You need to create connection string and most of the information you need is covered in the two links below. Hope this helps.

http://www.connectionstrings.com

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000010.asp

|||The problem is in your connection string. You have a typo: "Truster_Connection=yes" should be "Trusted_Connection=yes".

Wednesday, March 21, 2012

Invalid Keyword Color

Yesterday I installed SQL Server 2005 (April CTP) side-by-side with SQL
Server 2000. Everything appeard to go just fine except for when I went to
view my stored procedures or do anything in query analyzer most of the
reserved keywords do not appear in the default blue font anymore. For
example, CREATE PROCEDURE the word Create is in black but Procedure appears
in blue...ORDER BY, Order appears in black and By appears in Blue. This onl
y
happens when I am in SQL Server 2000, anything I do in 2005 appears
correctly. Any ideas?I was able to find the answer in another newsgroup. Apparently this is a
known issue with 2005 beta. What you need to do is find the sqllex.dll,
normally found in C:\Program Files\Microsoft SQL Server\80\Tools\Binn and
re-register this file. This fixed the problem for me.sql

Monday, March 19, 2012

Invalid cursor state

[Microsoft][ODBC SQL Server Driver]Invalid cursor state
Having a few of these reported in our application logs as it calls a sql
server stored proc stemming from ODBC that references SQLFetch
The stored proc does not use a cursor
Please exhibit some patience and don't post the same question multiple
times.
What on earth does your stored procedure do? Should we guess? Do you have
any problems when you call the stored procedure directly (without using the
app)? How about if you use a different connection method (e.g. OLEDB
instead of ODBC)?
"Hassan" <hassan@.test.com> wrote in message
news:OUHYKlXUIHA.5160@.TK2MSFTNGP05.phx.gbl...
> [Microsoft][ODBC SQL Server Driver]Invalid cursor state
> Having a few of these reported in our application logs as it calls a sql
> server stored proc stemming from ODBC that references SQLFetch
> The stored proc does not use a cursor
|||Hi Hassan
My guess is that it is a DTC issue, but as Aaron points out all three of the
posts you have made since April 2007 for this give very little information.
John
"Hassan" wrote:

> [Microsoft][ODBC SQL Server Driver]Invalid cursor state
> Having a few of these reported in our application logs as it calls a sql
> server stored proc stemming from ODBC that references SQLFetch
> The stored proc does not use a cursor
>

Friday, March 9, 2012

Invalid character value for cast specification

Hi: gurus,
I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
thing works fine if I don't use the datetime type column in sql. If I use the
datetime column in sql I am getting this message "Invalid character value for
cast specification"
Here is my XSD, I am not sure how to map sql annotation for date time field
"effdate" (do I have to) for this XSD. I will really appreciate your response.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="BillerInfo" sql:relation="BillerInfo">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="blrid" type="xsd:string"/>
<xsd:element name="acdind" type="xsd:string"/>
<xsd:element name="effdate" type="xsd:dateTime"/>
<xsd:element name="trnaba" type="xsd:string"/>
<xsd:element name="billername" type="xsd:string"/>
<xsd:element name="billerclass" type="xsd:string"/>
<xsd:element name="dmpprenote" type="xsd:boolean"/>
<xsd:element name="dmppayonly" type="xsd:boolean"/>
<xsd:element name="blroldname" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I guess the problem most likely lies in your data file. I assume that you
have a datetime value which is not matching with the xsd:dataTime format.
If you don't have the control on the input data, you may specify xsd:string
instead of xsd:dataTime.
Thanks.
"Rashid" <Rashid@.discussions.microsoft.com> wrote in message
news:FE70E9A6-B2AF-4BD4-B0DC-88425795A2F1@.microsoft.com...
> Hi: gurus,
> I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
> thing works fine if I don't use the datetime type column in sql. If I use
the
> datetime column in sql I am getting this message "Invalid character value
for
> cast specification"
> Here is my XSD, I am not sure how to map sql annotation for date time
field
> "effdate" (do I have to) for this XSD. I will really appreciate your
response.
>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="BillerInfo" sql:relation="BillerInfo">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="blrid" type="xsd:string"/>
> <xsd:element name="acdind" type="xsd:string"/>
> <xsd:element name="effdate" type="xsd:dateTime"/>
> <xsd:element name="trnaba" type="xsd:string"/>
> <xsd:element name="billername" type="xsd:string"/>
> <xsd:element name="billerclass" type="xsd:string"/>
> <xsd:element name="dmpprenote" type="xsd:boolean"/>
> <xsd:element name="dmppayonly" type="xsd:boolean"/>
> <xsd:element name="blroldname" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>

Sunday, February 19, 2012

Internet password attacks

I have opened port 1433 on our firewall to allow employees to access our SQL
server database and immediately we have received aggressive password attacks
,
mostly against the 'sa' user id. The 'sa' passoword is strong, but I'm
annoyed with the attacks that make my log files huge and cause unncessary
hits to the server.
Is there a way to configure SQL Server to force a delay between login
attempts or to temporarily lockout a user out as can be done with Windows
Server?
Many Thanks!Microsoft refuses to fix this glaring problem in SQL 2000. You can use a
port monitor to get the IP numbers then block them with a firewall or use
IPsec to block the IP numbers. It is a never ending job as new drones will
be attacking every day.
Microsoft's reply is that you shouldn't be doing this. Of course, they will
be glad to sell you a copy of SQL Server 2005 that does fix this (non)
problem.
With a strong password, the attackers will never be successful, but they can
eat up a lot of bandwidth trying.
Russ Stevens|||So you can confirm SQL 2005 does address this issue? Do you have details on
how?
I have port 1433 and 1434 open also going thru a Virtual IP, so far no
attacks.
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:um$43caqFHA.3524@.tk2msftngp13.phx.gbl...
> Microsoft refuses to fix this glaring problem in SQL 2000. You can use a
> port monitor to get the IP numbers then block them with a firewall or use
> IPsec to block the IP numbers. It is a never ending job as new drones will
> be attacking every day.
> Microsoft's reply is that you shouldn't be doing this. Of course, they
> will be glad to sell you a copy of SQL Server 2005 that does fix this
> (non) problem.
> With a strong password, the attackers will never be successful, but they
> can eat up a lot of bandwidth trying.
> Russ Stevens
>|||Rob,
<<So you can confirm SQL 2005 does address this issue? Do you have details
on
how?>>
SQL 2005 uses the normal Windows 2003 logon stuff - ie - you can allow 3
unsucessful logins, then have a lockout period of 30 minutes or whatever
(you specify both). I haven't actually verified this - it is in the docs but
it doesn't work under WinXP - after much searching I found that this feature
will only work on Win2003 server and I haven't installed the Beta on a
server yet.
<<I have port 1433 and 1434 open also going thru a Virtual IP, so far no
attacks.>>
Depending on how you are looking you may not notice them. Some drones are
just dialup - you would have a hard time seeing the once per second login
attempt on the bandwidth. Some drones are on high speed connections - will
give you a straight line on your bandwidth (30 or more connection attempts
per second). To see if anyone is trying, run the SQL Profiler and setup a
trace to record unsuccessful logins. They won't show up in your normal
events log. If 1433 is open to the Internet you can almost bet someone is
trying to get in during some parts of the day. Not sure why you would want
udp 1434 open - make them work a little harder <g>.
Russ Stevens|||Unfortunately it isn't a bug (where bug = coding error) in SQL Server 2000.
It isn't even a design flaw in the classic sense. It is a missing feature
set dating back to the original Sybase decisions made 20 years ago.
Microsoft's fix was to add integrated (aka Windows) security and push people
not to use the legacy SQL Server security stuff at all. That fixes the
problem. But despite Microsoft's desire to do away with the legacy security
stuff it is still heavily used. Partially for bad reasons (like, it just
being easier to embed a password in the application) and partially for good
reasons (like, we have to access it from a Unix system). So for SQL Server
2005 they are finally acknowleding that and re-working the Sybase-derived
stuff to use the full set of Windows password protections. But I doubt it
is something of a scope that it could be easily (mostly meaning safely and
reliably) backported into a SQL Server 2000 service pack. Not impossible of
course, but just well beyond the scope of a service pack.
Personally, I would NOT put a SQL Server 2000 directly on the Internet.
There are reasons beyond the password security issues that make me queasy
(not that I know of any specific vulnerability). The better way to enable
your users to access SQL Server remotely is to have them VPN in. That is
yet another reason Microsoft wasn't incented to enhance the legacy password
stuff earlier.
At a minimum you should use a non-standard port for your SQL Server if it is
going to be on the net. That should minimize the attacks (since I imagine
most of them are built on the premise that SQL Server uses 1433).
Hal Berenson, President
PredictableIT
Phone: 805-212-1025 ext 101
hberenson@.predictableit.com
Helpdesk: 805-212-1024 ext 1
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:um$43caqFHA.3524@.tk2msftngp13.phx.gbl...
> Microsoft refuses to fix this glaring problem in SQL 2000. You can use a
> port monitor to get the IP numbers then block them with a firewall or use
> IPsec to block the IP numbers. It is a never ending job as new drones will
> be attacking every day.
> Microsoft's reply is that you shouldn't be doing this. Of course, they
> will be glad to sell you a copy of SQL Server 2005 that does fix this
> (non) problem.
> With a strong password, the attackers will never be successful, but they
> can eat up a lot of bandwidth trying.
> Russ Stevens
>|||Hal,
<<It is a missing feature
set dating back to the original Sybase decisions made 20 years ago.
Microsoft's fix was to add integrated (aka Windows) security and push people
not to use the legacy SQL Server security stuff at all>>
Its refreshing to hear someone NOT say "SQL server was not designed to allow
open Internet access". As you mention, it was designed exactly for that
originally. Of course, this is further compounded by the fact that you can't
change the sa account. Microsoft recommends you rename the Administrator
account on its servers so that a hacker needs to guess both the user name
and a password. Given that the sa account is fixed, the hacker only needs to
guess a password. Then, on top of that, for years, the default password for
the sa account was an empty string. So hackers knew the login name and knew
the password for lots of SQL servers without having to do anything. It is
now a little harder as they try to crack the password using a dictionary
attack. Put a new SQL server online, and the hacks will start within an
hour.
I think Microsoft could easily fix this in a service pack if they wanted to.
They already have the necessary code for tracking unsuccessful logins (in
Profiler). They don't need to tie it in to group policy, etc. to fix this in
SQL 2000 - after x unsuccessful tries, block out y for z time and allow the
sa account to be renamed. Using another port doesn't help much - the hackers
can easily determine the SQL port.
Sure, the SQL 2005 way is the best way to do it (tied in to the OS), but
anyway you do it is better than the SQL 2000 way. Just imagine the amount of
Internet bandwidth that would then become available for something useful
<g>.
Russ Stevens|||Allow the SA account to be renamed in a service pack? You are mad. That
would break the entire product and require many thousands of lines of code
to be changed (not to mention all the customer apps that would be broken).
Put in a hard coded delay (z) after x failed login attempts for a particular
account (y), with no user interface for controlling x or z? That might be
reasonable. An even easier change would be to just insert a timeout-1
second delay before responding to a TDS Login packet that contained an
invalid password. That would effectively kill the automated attacks without
breaking anything (important). Unfortunately, it is likely to be a long
time until the next SQL Server 2000 Service Pack and that will (I'm
guessing) be little more than a hotfix rollup. So we've probably missed the
boat on doing anything for SQL Server 2000.
Hal Berenson, President
PredictableIT
Phone: 805-212-1025 ext 101
hberenson@.predictableit.com
Helpdesk: 805-212-1024 ext 1
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:u7sJiP%23qFHA.3600@.TK2MSFTNGP10.phx.gbl...
> Hal,
> <<It is a missing feature
> set dating back to the original Sybase decisions made 20 years ago.
> Microsoft's fix was to add integrated (aka Windows) security and push
> people
> not to use the legacy SQL Server security stuff at all>>
> Its refreshing to hear someone NOT say "SQL server was not designed to
> allow open Internet access". As you mention, it was designed exactly for
> that originally. Of course, this is further compounded by the fact that
> you can't change the sa account. Microsoft recommends you rename the
> Administrator account on its servers so that a hacker needs to guess both
> the user name and a password. Given that the sa account is fixed, the
> hacker only needs to guess a password. Then, on top of that, for years,
> the default password for the sa account was an empty string. So hackers
> knew the login name and knew the password for lots of SQL servers without
> having to do anything. It is now a little harder as they try to crack the
> password using a dictionary attack. Put a new SQL server online, and the
> hacks will start within an hour.
> I think Microsoft could easily fix this in a service pack if they wanted
> to. They already have the necessary code for tracking unsuccessful logins
> (in Profiler). They don't need to tie it in to group policy, etc. to fix
> this in SQL 2000 - after x unsuccessful tries, block out y for z time and
> allow the sa account to be renamed. Using another port doesn't help much -
> the hackers can easily determine the SQL port.
> Sure, the SQL 2005 way is the best way to do it (tied in to the OS), but
> anyway you do it is better than the SQL 2000 way. Just imagine the amount
> of Internet bandwidth that would then become available for something
> useful <g>.
> Russ Stevens
>|||Hal,
<<Allow the SA account to be renamed in a service pack? You are mad>>
No - the SP doesn't rename it, it gives the SQL admin the ability to change
it. If he has apps that use it, then he can fix them first or leave as is.
<<Unfortunately, it is likely to be a long
time until the next SQL Server 2000 Service Pack and that will (I'm
guessing) be little more than a hotfix rollup. So we've probably missed the
boat on doing anything for SQL Server 2000.>>
We have been missing the boat for many years - Microsoft just refuses to fix
it (this is not a new issue <g> ).
Thanks
Russ Stevens|||On Mon, 29 Aug 2005 08:22:51 -0400, Russell Stevens wrote:

> <<Allow the SA account to be renamed in a service pack? You are mad>>
> No - the SP doesn't rename it, it gives the SQL admin the ability to chang
e
> it. If he has apps that use it, then he can fix them first or leave as is.
There are many many parts *internal* to SQL server that depend on the sa
account being named sa. A service pack that changes them all to tolerate a
renamed sa account is a dangerous thing to do.|||I've been programming for 20+ years and to me there are a number of
inexpensive solutions to this problem that Microsoft could deploy, but they
have delibertly choosen not to for whatever motive. The fact that no
Micrsoft MVP has responded to this post as I have seen in most other posts,
further backs this opinion.
Is there a Microsft MVP out there willing to touch this issue?
"Ross Presser" wrote:

> On Mon, 29 Aug 2005 08:22:51 -0400, Russell Stevens wrote:
>
> There are many many parts *internal* to SQL server that depend on the sa
> account being named sa. A service pack that changes them all to tolerate
a
> renamed sa account is a dangerous thing to do.
>