VB.Net and MSDE Installation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

MSDE (2000)

VB.Net and MSDE Installation

Author

Topic

mmatsumura
Starting Member

6 Posts

Posted - 2003-08-13 : 15:09:56

Hello,

I have been having a problem and am hoping that someone provides me the resolution.

My goal is to deploy MSDE to employees that do not have it. I've been following the instruction as either on-line provided help sites, WebCast movies and books, but haven't been able to get things to work. The language I have trying to been using is VB.Net which is very, very new for me. In an article I've read from Microsoft titled "Embedding MSDE 2000 Setup into the Setup of Custom Applications" had a FAQ section which happens mention exactly my problem. Below I will show you what is written:

*****
Q: My application is unable to connect to MSDE 2000. I receive an error message that says that the connection attempted is not a trusted connection. How can I fix that?

A: This occurs because MSDE 2000 uses Windows authentication by default. Windows authentication is more secure than SQL Server authentication. You must alter code within your custom application to use the secure login to overcome this error rather than using SQL Server authentication.
*****

So, this is where I am having an understanding problem. What code would I need to alter and how??? I really hope that someone would send me a plain-text answer rather that links to Web pages.

Thank You!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-13 : 15:16:13

You are trying to use sql authentication to connect to the MSDE server. Instead use Windows authentication. You could alter MSDE so that it allows sql authentication, but this is not recommended. Change your connection string so that it uses an account in Windows. You could start simply with an account that has administrative privileges on the server, but only to test it. Let us know how that goes and we'll guide you through the rest of it.

Tara

mmatsumura
Starting Member

6 Posts

Posted - 2003-08-13 : 15:47:38

Tara,

Thank you SO much for answering the posting!

Okay, here is what I've been doing:

1. Installed MSDE by executing "sql2ksp3.exe" which is what I have on this local computer.
2. After running that file, I have been following the instructions in an article titled "MSDE 2000 for Developers Using Visual Studio.NET". The instructions tell me to change to the directory where I've extracted the files and run the following command:

Setup.exe /qb+ INSTANCENAME=MIKEMAT DISABLENETWORKPROTOCOLS=1 SAPWD=SOMETHING

So, that is what I did to installed MSDE. When I try to install the VB.NET program that I have writen, I get an error that tells me to have an SAPWD. That's as far as I have been able to go and where I am begging for some help!

I am willing to walk through this from start to finish if you are willing to assist.

Thanks

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-08-13 : 18:15:31

Post us what connection string you are using. Please change any passwords in the string before posting! If I see the string, I can tell you how to modify it to get connected to your DB.

Here's a good place to look for that connection stuff. The CNN string also depends on what Class you are using in VB.net to connect. Look at this link. It should give you all the info you need. The only thing I think you need to change is you need to add your instance name in the connection string, like this


oSQLConn.ConnectionString = "Data Source=(local)\yourinstancenamehere;" & _
                            "Initial Catalog=mySQLServerDBName;" & _
                            "Integrated Security=SSPI"

http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm#SQLClientManagedProvider

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-13 : 18:17:57

Bring up a cmd window. Navigate to the directory where osql.exe exists. Then type this:

osql -SServerName\MIKEMAT -Usa -PYourSAPassword

Change ServerName to the name of the server. Change YourSAPassword to whatever it is. Do you get an error or do you get ">"? If you get ">", then you are connected fine.

Tara

mmatsumura
Starting Member

6 Posts

Posted - 2003-08-13 : 20:15:24

MichaelP,

Many thanks... Below is what I have been trying for the form:

Imports System.Data.SqlClient

Public Class Form1
Inherits System.Windows.Forms.Form

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim cn As New SqlConnection("server=MMATSUMURA1\MYTEST" & _
"Database=Northwind" & _
"Integrated Security=SSPI")
Dim sql As String = _
"SELECT ProductName AS Product, CategoryID AS Category, UnitPrice AS Price, UnitsInStock AS [# In Stock];" & _
"FROM Products "
Dim cmd As New SqlCommand(sql, cn)
Dim sda As New SqlDataAdapter(cmd)
Dim ds As New DataSet

sda.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)

End Sub
End Class

I also have written this VBS file which is included in the Setup and Deployment project.

Option Explicit

Sub SLUtilShellExec( strCmdLine )
Dim ObjShell
Set ObjShell=CreateObject("WScript.Shell")
ObjShell.Run strCmdLine, 0, True
Set ObjShell = Nothing
End Sub

SLUtilShell "net start MSSQL$MyTest"
SLUtilShell "osql -E MATSUMURA -S MMATSUMURA1\MyTest -i ""C:\Documents and Settings\mmatsumura\Desktop\Backup\MSDETest\SQLData\instnwnd.sql"" "

Once I build the Deployment project I've been modifying the .msi file using ORCA which is what I have seen on a Webcast. The final thing I've been trying is to install the application, which is where it doesn't work. That's where the SAPWD message comes up.

Hope this gives you something to review and that you'll send another answer.

Many Thanks!

quote:
Originally posted by MichaelP

Post us what connection string you are using. Please change any passwords in the string before posting! If I see the string, I can tell you how to modify it to get connected to your DB.

Here's a good place to look for that connection stuff. The CNN string also depends on what Class you are using in VB.net to connect. Look at this link. It should give you all the info you need. The only thing I think you need to change is you need to add your instance name in the connection string, like this

oSQLConn.ConnectionString = "Data Source=(local)\yourinstancenamehere;" & _
"Initial Catalog=mySQLServerDBName;" & _
"Integrated Security=SSPI"

http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm#SQLClientManagedProvider

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

mmatsumura
Starting Member

6 Posts

Posted - 2003-08-13 : 20:17:20

Tara,

Many thanks again... Below is what I have been trying for the form:

Imports System.Data.SqlClient

Public Class Form1
Inherits System.Windows.Forms.Form

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim cn As New SqlConnection("server=MMATSUMURA1\MYTEST" & _
"Database=Northwind" & _
"Integrated Security=SSPI")
Dim sql As String = _
"SELECT ProductName AS Product, CategoryID AS Category, UnitPrice AS Price, UnitsInStock AS [# In Stock];" & _
"FROM Products "
Dim cmd As New SqlCommand(sql, cn)
Dim sda As New SqlDataAdapter(cmd)
Dim ds As New DataSet

sda.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)

End Sub
End Class

I also have written this VBS file which is included in the Setup and Deployment project.

Option Explicit

Sub SLUtilShellExec( strCmdLine )
Dim ObjShell
Set ObjShell=CreateObject("WScript.Shell")
ObjShell.Run strCmdLine, 0, True
Set ObjShell = Nothing
End Sub

SLUtilShell "net start MSSQL$MyTest"
SLUtilShell "osql -E MATSUMURA -S MMATSUMURA1\MyTest -i ""C:\Documents and Settings\mmatsumura\Desktop\Backup\MSDETest\SQLData\instnwnd.sql"" "

Once I build the Deployment project I've been modifying the .msi file using ORCA which is what I have seen on a Webcast. The final thing I've been trying is to install the application, which is where it doesn't work. That's where the SAPWD message comes up.

Hope this gives you something to review and that you'll send another answer.

Many Thanks!

quote:
Originally posted by tduggan

Bring up a cmd window. Navigate to the directory where osql.exe exists. Then type this:

osql -SServerName\MIKEMAT -Usa -PYourSAPassword

Change ServerName to the name of the server. Change YourSAPassword to whatever it is. Do you get an error or do you get ">"? If you get ">", then you are connected fine.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-14 : 11:12:51

Don't try it in VB, try it in a cmd window. We need to see if you can connect outside of VB first. Because if you can't, VB isn't going to work either. Also, in your osql command, remove MATSUMURA after -E. -E means use whatever account is currently logged into the machine, so you don't have to provide the user name, it already knows it.

Tara

mmatsumura
Starting Member

6 Posts

Posted - 2003-08-14 : 15:04:00

Tara,

Thanks again...

I have been able to connect from a DOS prompt. All I did was type "osql -E -SMMATSUMURA1\MYTEST" which gave me a '1>' prompt. From that point things seem to workout okay. Also, I have had the connection work either with or without MATSUMURA.

So, any idea on what's causing my problem?

Thank You,

Mike

quote:
Originally posted by tduggan

Don't try it in VB, try it in a cmd window. We need to see if you can connect outside of VB first. Because if you can't, VB isn't going to work either. Also, in your osql command, remove MATSUMURA after -E. -E means use whatever account is currently logged into the machine, so you don't have to provide the user name, it already knows it.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-14 : 15:49:50

Well the problem isn't with SQL Server. That's why I had you do the osql command in a cmd window. I am not a VB programmer, so I can't help you out with the code, I was just ensuring that everything was correct with SQL Server. Somebody else here will be able to help you out though. Good luck!

Tara

mmatsumura
Starting Member

6 Posts

Posted - 2003-08-14 : 16:07:46

Tara,

I do wish to thank you very much for answering my questions and giving me a hand. Rest assured, I am not going to quit until I either figure this out OR fall asleep!

Thanks,

Mike

quote:
Originally posted by tduggan

Well the problem isn't with SQL Server. That's why I had you do the osql command in a cmd window. I am not a VB programmer, so I can't help you out with the code, I was just ensuring that everything was correct with SQL Server. Somebody else here will be able to help you out though. Good luck!

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources