Error when using SSL encryption for SQL server 2000

Hi,
I am having trouble connecting to SQL server 2000 using SSL.
My environment is as follows:
1. Microsoft Certificate server running on a Windows 2000 server machine.
2. SQL Server 2000 running on a Win NT machine with SP6 installed. SQL
Server service pack 3a has been installed.
3. Client machine running Windows XP with SQL Server service pack 3a
installed.
We use a type 4 JDBC secure driver to connect to SQL server from our Java ap
plication running on the client machine.
We also setup the certificate as described in the article 276553 - HOW TO: E
nable SSL Encryption for SQL Server 2000 with Certificate Server.
The installation of the certificate was successful. However, when I tried to
connect to the SQL server using SQL Query Analyzer, I received the followin
g error message.
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Encryption not supported on SQL Serv
er
Thanks,
Chacko.Usually, you receive that error if you enable SSL from the client and the
server doesn't have the certificate.
But, the bad news is you won't be able to use SSL with a type 4 Java driver.
The implementation of protocol encryption requires the driver to access
SSL. In
particular protocol encryption uses SSL APIs implemented in NT. Type 4 JDBC
drivers have a problem in that they are not allowed to directly call system
dlls. There is no library in Java 1.4 or below that emulates the protocol
encryption behavior of SSL from Windows NT.
If your JDBC application requires protocol encryption to SQL 2000,
you will have to use alternate method of encryption such as IPSEC or use a
suitable SSL enabled Type 3 JDBC Driver.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
Thanks for your response.
We did generate and install the certificate using the web browser and not us
ing MMC since it is a Win NT 4.0 box. When we look at the certificate on the
database server machine via the IE properties applet, the certificate shows
up fine as described in th
e Microsoft setup document. So, I am not sure where I am going wrong. Is the
re any way to trouble shoot this issue?
Also, we are not even trying the connectivity with the type 4 JDBC driver ye
t even though that is what we want to do ultimately. However, right now we a
re using Query Analyzer on the client machine to test the communication. We
get the error when we try a
nd login to the database via Query Analyzer.
About the type 4 JDBC driver, the documentation from i-net OPTA type 4 JDBC driver says
that it supports SSL for SQL Server 2000. Am I misunderstanding something here? You ca
n find information about i-net OPTA at [url]http://www.inetsoftware.de/English/Produkt[
/url]
e/OPTA/default.htm
Your thoughts are greatly appreciated.
Thanks Kevin.
Chacko.

Error when trying to edit a DTS package - please help

SQL2000 sp2 and sp3a
I'm having trouble getting into DTS design view on a
particular pc. I can get in fine on other pcs. This
apparently started happening just after installing sp3a on
my local machine. The pc I am using can't be rebuilt and
I can't use a different pc. I get a couple of error
messages. After clicking design package, I ge the
following error "Error occurred during creation of a DTS
Package". After clicking ok, I get "The selected package
cannot be opened. The DTS Designer has been closed".
This happens on all packages on all servers (and local)
for the pc I am using. I found one bit of information on
the net, but it didn't help. It was at:
http://www.sqldts.com/default.aspx?228 I then uninstalled
sql and reinstalled. That still didn't work. I've
uninstalled and put sp2, sp3a, different combinations a
few times and still no luck. Has anyone experienced this
or does anyone have any suggestions?
Thanks,
Van JonesThis can happen if the MDAC installation gets corrupted. Since you have
installed SQL Server SP3 then you have MDAC 2.7 SP1. The only way to
correct the MDAC installation would be to install MDAC 2.8 on this server.
Unless this is will cause a problem for you, you can possibly fix the
problem by installing MDAC 2.8.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Error updating a DateTime field

Hi, I'm having trouble updating a DateTime field in my SQL database. Here is what I'm trying to do...I retrieve the existing value in the DateTime field (usually a bum date like 1/1/1900 00:00:00:00), then put it in a variable. Later, depending on some conditions, I'll either update the DateTime field to today's date (which works great) or set it back equal to the existing value from the variable (this one messes up and says "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. "). There is a ton more than this but here are the relevant snippets:
<code>
Dim CompDate As DateTime
Dim aComm As SQLCommand
Dim aReader As SQLDataReader
Dim bSQL,bConn As String
bSQL= "SELECT CompleteDate,StatusOfMarkout FROM Tickets WHERE TicketName=" _ & CHR(39) & Trim(Ticket.Text) & CHR(39)
bConn = serverStuff aConn = New SQLConnection(bConn)
aComm = New SQLCommand(bSQL,aConn)
aConn.Open()
result = aComm.ExecuteReader()
'fills controls with data
While result.Read()
CompDate = result("CompleteDate")
PreviousMarkoutStatus.Text = result("StatusOfMarkout")
End While
result.Close()
aConn.Close()
sSqlCmd ="Update OneCallTickets CompleteDate=@.CompleteDate, StatusOfMarkout=@.StatusOfMarkout WHERE TicketFileName=@.TicketFileName"
dim SqlCon as New SqlConnection(serverStuff)
dim SqlCmd as new SqlCommand(sSqlCmd, SqlCon)
If Flag1List.SelectedItem.Value = "No Change" Then
SqlCmd.Parameters.Add(new SqlParameter("@.Flag1", SqlDbType.NVarChar,35))
SqlCmd.Parameters("@.Flag1").Value = PreviousMarkoutStatus.Text
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = CompDate
Else
SqlCmd.Parameters.Add(new SqlParameter("@.Flag1", SqlDbType.NVarChar,35))
SqlCmd.Parameters("@.Flag1").Value = CurrentStatus.Text
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = Today()
End If
SqlCon.Open()
SqlCmd.ExecuteNonQuery()
SqlCon.Close()
</code>
Can anybody help me with this? Thanks a bunchIs your CompDate a DateTime or is it a string? #1 make sure it is a DateTime.
You might also try using the Convert function in your procedure to convert the argument to a SQL DateTime and see what happens.|||

Yes, the value I'm reading from the database is 100% a datetime. Could you give me an example of how to convert? Thanks

|||

I think you need the DateDiff function of both VB .NET and SQL Server and also change your data type to SmallDateTime it has less resolution if the seconds are not important. Try the links below for sample code using TimeSpan with DateDiff. Hope this helps.
http://blogs.msdn.com/vbfaq/

http://www.stanford.edu/~bsuter/sql-datecomputations.html

|||Hmm, I guess I don't understand. I read through those links and all I could find was how to calculate the difference between two dates. I couldn't really find an answer in there?|||CONVERT(DATETIME, Your_Date_Field)|||

When I try that here:
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = CONVERT(DATETIME, CompDate)
I get a Compiler Error Message: BC30684: 'CONVERT' is a type and cannot be used as an expression.
Should I be using it somewhere else?

|||I was assuming you are using a stored procedure to do the work. The CONVERT function is a T_SQL function and should be done inside of a stored procedure or SQL statement.