SQL Express

OK, I finally decided to make the change from Access to SQL. I downloaded SQL Server Express, and MSSM and I created my database. Which was pretty spiffy.

I then went to incorporate it, as a dataset, into my project. Lo and behold the damn thing says I don't have permission to do so and after spending hours doing different things to get permission I am still at a loss as to how to use it.

Is there some secret society with a secret handshake that that I must be initiated into to be able to do anything with this database?

You need to be very specific about EXACTLY what you did and EXACTLY what happened. Vague descriptions are of limited value because it leaves us with the only real option of describing the entire process end to end.

If you had access via SSMS, you should be able to get access via the exact same connection method in your VB code.

To see how SSMS is connecting, right click the DATABASE from the EXPLORER pane and select properties. Lower left corner of that popup has a VIEW CONNECTION PROPERTIES link.

Is the authentication method Windows or SQL in this popup? Note the username.

How do they differ from what you are using in code?

Also, are you connecting from the same machine - VB code and SQL Server instance running on the same box? If you are doing remote connections, they are turned off by default and can be a challenge to turn on fully.

Show us some code, please.

Quote:

---

Originally Posted by jmcilhinney

You need to be very specific about EXACTLY what you did and EXACTLY what happened. Vague descriptions are of limited value because it leaves us with the only real option of describing the entire process end to end.

---

I installed SQL Express 2019 and SMSS. I used SMSS to setup a database, pretty spiffy I might add, and then after failing to be able to create a dataset, using the wizard in Visual Studio (due to a lack of permission), I tried several different suggestions that I looked up. In every single instance the end result was the same. I needed some permission that I clearly did not have.

The user name is GLENNWALKERSITE\glenn. I tried both the whole username and just the glenn part. This is the identical problem I had when I attempted to use SQL a couple of years ago.

My description is not vague and is exact, up to the point I attempted other suggested methods. To reiterate the exact sequence, I loaded SQL Express, then SMSS. I started SMSS and the screen noted below was the first thing displayed. Windows Authentication is the ONLY option that I have ever been able to use. When I try to login for SQL Server Authentication, as instructed, I get the second message shown here.

Having said all that, instead of waiting for one of you to come up with the answer, I decided that was not a big enough problem and went barbaric and did a reinstall of Visual Studio on my machine. Now I cannot even create a dataset at all.

Instead it now sends me to

Quote:

---

https://aka.ms/winforms/Databinding

---

, which I have yet to be able to obtain an interpretation that I can understand.

I figured that two problems, completely unrelated to each other, are better than one. But I would like to resolve the permissions with SQL express.

So, if you will, tell me what EXACTLY I should present to give you a better idea of what the problem (with permissions) might be. The sequence of events is pretty straight forward.

Attachment 183436


Attachment 183437

Aha.
I think you must enable TCP access . I have to take a look on how to do that because I don't remember by heart but I will just google it...

Edit:
On the Start menu, click All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager. Click SQL Server 2012 Services. Expand the SQL Server 2012 Network Configuration node, and then select Protocols for MSSQLServer (SQL Instance Name) . Right-click TCP/IP, and then click Enable.

Also here:
https://docs.microsoft.com/en-us/ans...-end-of-t.html

In the screenshot that you added, you are using Windows Authentication. It looks like you also tried GLENNWALKERSITE\glenn as a SQL Server authentication. I don't think that's your problem, which I'll get to in a minute, but you probably do need to be clear about which way you are accessing the DB. Your connection string is probably somewhat different for the two different routes you are trying to take.

As to the issue, it is possible that a slightly hidden setting is not correct. This is an annoyance I have with SQL Server. Ultimately, you can do all kinds of things with SQL Server. You can configure it in a wide variety of different ways for a wide variety of different scenarios. There's an obvious advantage to that, but one noticeable disadvantage, as well, since there are so MANY potential settings.

Go to the start menu and look for the SQL Server folder (you may have a couple different versions, so get the right one). In there, you should find the SQL Server Configuration Manager. Use of that tool can be a bit opaque, in my view. When you open that, there will be two panes (or pains). In the right pane you will probably see SQL Server (almost certainly running), SQL Server Agent (probably stopped), and perhaps SQL Server Browser. Don't worry about that SQL Server Agent, that's not the issue. If SQL Server isn't running, that would be an issue, but it'll be running, so that's not the issue....unless it isn't running, but it will be.

On the left pane, take a look at the SQL Native Client configuration. There are likely going to be two of those. Also look at the SQL Server Network Configuration. Which protocols do you have enabled?

I'm a bit vague on this, as my exploration in this area has yielded some peculiar results. It may also not quite apply. I've been dealing with this kind of a thing across a LAN, whereas it sounds like you are dealing with it on your local machine. At one point, I thought I had to have Named Pipes enabled. I figured that out through experimentation, and then more experimentation showed that I was wrong. I do have to have TCP/IP enabled, though it is possible that is due to my using the DB across a LAN.

EDIT: I was writing that as Sapator was also writing roughly the same thing. He was much shorter about it, though, so he won the race.

The problem with both my response and Sapators is that you are likely thinking: WHY should I need to do that? Why isn't it done automatically? And that's the problem. I'm not sure that it IS necessary. I'm quite certain that I didn't do that on one of my systems where I only used SQL Server locally. I'm quite certain that I ran into the same error you are getting when I tried to gain access across a LAN to a different system, but it sounds like you are running local, in which case, while it is worth checking your settings, I'm not sure this will solve the problem.

No problem Shaggy, you are much more explanatory than me,as always.
The thing is I remember the exact opposite, enabling pipes on every single server but it might have to do with the fact that I was using asp.net constantly. For now we have our admins handle most of the security so I don't have to lift a finger. :)

I'm ready to lift a finger, currently. We just moved into a new office where I'm sitting next to a howling server room.

Our main building have 2 racks packed with servers and every individual building have one rack so if i where to lift a finger, I wouldn't have putted it down till August.

The finger I'm ready to lift is more expressive.

If you guys are done flipping each other off, I have an update. I had both a 2017 and 2019 version of SQL Express. The 2017 did not have a configuration manager, so by process of elimination I reviewed the one for 2019.
So I enabled the named pipes and TCP/IP in the nework configuration. I have two instances, so I did both, even though I know which one I am using. Additionally, there were two Natives, so I checked both Nativity scenes and enabled them.

The fact that the server is running would suggest to me that is why I am unable to copy and paste the database to the location I would like to run it from. Can I just turn the server off and place the database where I would like to locate it? That would be smoot!

Anyway, what do I do now?

You checked a Nativity scene right after Christmas??? Jesus, that's a good one!

Copy and paste the database? Can you even DO that in SQL Server? In Access, your DB is generally just a file like any other file. SQL Server is much different. Others will have better information about that, and I'll be interested to see what they have to say. My use of SQL Server on my own computers has been fairly basic. I need to be changing that understanding in the next year, but that's where it is at the moment. I don't believe you can move the database around the same way in SQL Server as was possible in Access. I could be wrong about that, though.

Quote:

---

Originally Posted by Shaggy Hiker

Copy and paste the database? Can you even DO that in SQL Server? In Access, your DB is generally just a file like any other file. SQL Server is much different. Others will have better information about that, and I'll be interested to see what they have to say. My use of SQL Server on my own computers has been fairly basic. I need to be changing that understanding in the next year, but that's where it is at the moment. I don't believe you can move the database around the same way in SQL Server as was possible in Access. I could be wrong about that, though.

---

Yes, you can, if you go to your task manager and turn sql server off. But still moot, since I am still unable to connect/open that database... except through the SQL Data Source window. So any idea what my next course of action would be?

Yes... you can stop SQL Server and copy the files... You can also do a "Detach Database" ... but then what? you need to re-start SQL Server and re-attach the files in order to use the database.

Next course of action? Stop trying to move he database, leave it where SQL Server thinks it should be and just connect to it through SQL Server.

-tg

Quote:

---

Originally Posted by techgnome

Yes... Stop trying to move he database, leave it where SQL Server thinks it should be and just connect to it through SQL Server.

-tg

---

That would be wonderful to connect to it through the Server. But that has, to date, been denied to me.

While I do prefer to move the database where I want it, rather than where they want it, that is not what I am after. I just want to be able to open the damn database that I created, and be able to use the database. To date, I am unable to open the database outside of the manager and use it. What good is a database that you are not allowed to open?

Everyone claims that SQL is so damned superior. Perhaps that is true. But if you can't use it, it is worthless! Access has plenty of problems, but at least it is available for use without a lot of games and BS!

Lets take this step by step as I got confused.
1)Are you able to log in to SQL server, never mind access and such, are you able to login to an empty database?
2)To copy access db you have to use the SQL Server Migration Assistant or the SQL Server Import Wizard, I would recommend the later. Am not sure at all that you can just attach and access database and work with it.I only have doubts because tech said that is possible else I would rule it out entirely.

Now I suspect that you are not able to log in at all in SQL.
Look at this:
https://www.sqlshack.com/how-to-inst...loper-edition/
Now the important picture is the one that says "Choose authentication mode (Windows authentication mode and mixed mode) and specify the SQL Server administrator" if windows authentication does not work then choose mixed mode. You then will login with username "sa" and the password you specify.

BEFORE doing all these, delete everything SQL related, uninstall , delete database burn the PC etc. We need a completely clean installation.

Is this what an Access user trying to do SQL Server and hitting a brick wall looks like?

No, in SQL Server you cannot copy your database -- it's not a single file.

No, in SQL Server you cannot connect to your database -- you connect to a server instance and *use* an available DB there.

Yes, you can backup a database to a single file.

Yes, you can place your database on a dedicated server in LAN/Internet and have no problems with 100s of simultaneous users.

Yes, you can be sure no one is using a hex editor to exfiltrate passwords from your database as long as your SQL Server has sane DBAs.

Now, why would you want to connect with SQL Security when Integrated Security just works and is more secure?

cheers,
</wqw>

I can access an SQL database ONLY through SSMS. I loaded this tool immediately after loading SQL Server Express. The database was one I created using SSMS. This database has been inaccessible to visual basic, as a dataset. I am, however, able to access this database through the SQL Datasource Window and the Server window. Additionally, I can create an SQL Database project with the database, although I have no idea what I can do with that. Additionally, I am unable to setup the SQL Express connection in anyway but as a windows authorization.

I have attempted, unsuccessfully, many different approaches to accessing the database (you would not believe how many different methods exist to achieve this end), but everyone has ended up with a permission issue. It is clear to me that I have either missed something fundamental (I am frequently not the sharpest tool in the shed), or this whole this whole SQL database thing is a hoax intended to ensnare people foolish enough to listen to the hype about SQL.

Quote:

---

No, in SQL Server you cannot copy your database -- it's not a single file.

---

That is true. It appears to me that there are at least two files required to access the database.

Quote:

---

No, in SQL Server you cannot connect to your database -- you connect to a server instance and *use* an available DB there.

---

Whatever. The fact remains that I can use the SMSS to open, modify, delete, create data to the database. That does me no good. I have to be able to create, modify, and delete data from Visual studio. Visual Studio does have tools for doing this and some of them, like the server window, allows this. But what I need to so be able to use Datasets. To date I am completely unable to do that because of permissions.

Quote:

---

Now, why would you want to connect with SQL Security when Integrated Security just works and is more secure?

---

I am willing to connect to the database anyway that I can if I can just get guidance as to how I can achieve that.


Sapator,

I am just about to take a shot at your suggestions... Just as soon as I clean up the mess I have made of everything after trying everything else. This is obviously not going to be an easy task.

Quote:

---

Originally Posted by gwboolean

I am willing to connect to the database anyway that I can if I can just get guidance as to how I can achieve that.

---

You have exactly 3.5 ways of achieving this.

1. The .Net Provider
2. OLEDB Provider
3. ODBC Driver
3.5 Legacy DB-Library

There are other drivers too mainly based on DTS protocol from Sybase in Linux world like PDO for pyhton, something for PHP and probably Ruby.

What do you currently use for working with your MDB databases?

Why would you want to connect to the database? Design new tables in Management Studio or query data from your LOB application?

cheers,
</wqw>

There is much more to security in SQL Server than there was in Access.

I don't use any of the VS datasource wizard stuff, so I have nothing to say about that. What you might try, to start with, is a simpler, raw, approach. Something like this:

Code:

---

Dim yourConnectionString = "some connection string here"

Using cn as New SqlClient.SqlConnectionString(yourConnectionString)
  cn.Open
End Using

---

That's good enough for a test. It would either work (which would do nothing, so you'd probably want to add a messagebox in case it DID work) or throw an exception. You could then just play around with the connection string. The site for that is here:

https://www.connectionstrings.com/sql-server-2019/

You almost certainly want one of the first two, though if you did a non-default installation then one of the others might be right for you.

That may leave the security question unanswered, but it's a start.

Quote:

---

You have exactly 3.5 ways of achieving this.

---

I am sure that you are correct. Having said that, nothing allows me to connect to an SQL database or the server.

Quote:

---

Why would you want to connect to the database? Design new tables in Management Studio or query data from your LOB application?

---

Quote:

---

Why would you want to connect to the database? Design new tables in Management Studio or query data from your LOB application?

---

The table I am wanting to access was created in SSMS. I have no idea what a LOB application is.

Quote:

---

hat do you currently use for working with your MDB databases?

---

Good question.

What I have is a database I created using SSMS The table resides in the DATA folder in one of the MSSQL folders. I suspect that this database is NOT the one I should be using with Visual Studio for a dataset. Certainly I am unable to use this database to create a dataset.

What I want is a detached database that I can locate in the project bin\debug file to use. The world would be a better place to live if I could accomplish this. I want this because a detached mdb is the ONLY thing I have ever been able to connect with.

A couple of years ago, I actually was able to create a database in SSMS, somehow detach it from the server, relocate the database and access it in Visual Studio, I have no idea how either now or then.

Bottom line is that any database I create in SSMS cannot be used to create a dataset, using any of the methods suggested to date.

Sequence of events:

1. Loaded SQL Server Express using the custom load to ensure that everything needed was included.
2. Loaded SSMS and created a database. The database created is in a MSSQL\DATA folder.
3. Attempted to use the dataset wizard to create a dataset with the SQL Database. There is NO connection method that would allow me to create an SQL dataset.

LOB: Line of Business, which is an application used internally by some business or entity for managing some of their data. That differentiates it from software created for external users.

Quote:

---

Originally Posted by gwboolean

2. Loaded SSMS and created a database. The database created is in a MSSQL\DATA folder.

---

I have a suspicion that this single line contains the whole of the problem. I haven't looked, but I have never used SSMS to create a database and been able to say what you said in the second statement. I can't say that I ever knew where the database was created, or could talk about it in that fashion. Therefore, that line makes me think that you are detaching the DB, or something like that, to create a file. You've talked about it a couple times as if SQL Server arranged data in a way that is at least similar to the mdb file that Access used.

I'm on a bit of a call, at the moment, but I'll take a look in a little while to see whether or not I even COULD make a statement like that one.

Yes, by default, SQL Server will put the mdf/ldf files in a {some redoncously long path}MSSQL/DATA folder ... and that's fine ... that's the default location for those files. If you detach them and move them to a new location, you'll need to go back into SQL Server and re-attach them - both of them - before the database can be used again.

Now, there used to be a standalone file format that you could use with Visual Studio, still required SQL Server to be running on the local computer, and you could attach them at will when making the connection in code ... I forget the exact name they were called ... It might be this kind of databases you experienced before ... and yes, those could be detached and moved about freely.

This connection string might be of some use.
https://www.connectionstrings.com/mi...ress-instance/

-tg

I suspect you are talking about sqlce, SQL Server Compact database.
That is definitely files in you project. I have done so a couple of years ago when I wanted a character catalog to move around.
I would never recommend it if you have means to connect to an SQL server at all of you application locations. I should also point out that I wanted to make a simple change to that database and I never found a way to do it with SSMS, I just opened the app in Visual Studio to change stuff.

So I don't recommend this but if you definitely want a file to move around as you proclaim, you can try it.

Quote:

---

Originally Posted by gwboolean

What I want is a detached database that I can locate in the project bin\debug file to use.

---

Then don't create the database in SSMS. If you do that then you are creating a database that is permanently attached to that specific SQL Server instance. If you want a database that is part of your application then you create it in VS. You add it just like you do any other project item. Open the Add New Item dialogue and then find then Service-based Database item template. That will add an MDF data file directly to your project. In the connection string, rather than using the Initial Catalog attribute to specify the attached database to connect to, you use the AttachDbFilename attribute to specify that your MDF file gets attach to the instance on demand. The data file will then be deployed along with your application and attached to the local instance of SQL Server Express when the application is run. Note that the data file is useless on its own. There MUST be an instance of SQL Server Express running on the local machine for the data file to be attached to.

Note that you don't even need to install SQL Server Express on your development machine if you're going to do it that way. LocalDB is installed with VS, which is a stripped down version of SQL Server Express for developers. You can add and use an MDF file with just that and then simply modify the connection string in the config file for deployment.