Connecting to an SQL Database.
-
Already two good answers in my absence, and I'd like to expand on them.
Bram van Kampen wrote:
The Tables were constructed and populated with the SQL management studio, and, also saved.
Which credentials did the Management Studio use to connect to the database? :rolleyes:
Bram van Kampen wrote:
They are shown in the studio as "dbo.Users", resp "dbo.Names". Is this the correct way of addressing these tables in a queery string, or should I leave out the 'dbo' part.
The dbo is the schema-name of the "database owner". In the ideal case, one would create a new schema and assign the appropriate rights to that. Whether you (want to) use the prefix in the SQL statements depends on how portable (or strict) you want your queries to work. Without specifying the schema, it's easier to reuse the query under a different schema. OTOH, I often even prefix the databasename and the server to identify a table;
SELECT
FROM server.databasename.schemaname.tablename
WHERE 1=1This way I'm pretty sure that I'm selecting exactly what has been specified, regardless of which is the "current" database, or even the current machine, and copy/paste errors show up as a parser-error before the command is executed. Omit the server/schema, and you can guess what happens when you do a "DELETE FROM Users" when you've selected the wrong server by accident.
Bram van Kampen wrote:
I have both Amin and User Access to this machine.
Yes, but that's local to your machine - the database-server has it's own security. There's two modes; first there's Windows Authentication, which takes the user that's currently logged into Windows to authenticate (and is DBO when creating a table). One doesn't need a password in that case, since you already provided a username/password when logging into Windows. That's the preferred way. Alternative, there's "mixed mode", where SQL accepts a username/password combination (with "sa" usually being the name of the admin account). First, you'll need to know how the server is configured. That's the same as when using the Management Studio, and as quoted above, you've already used it to login when creating the tables.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[
Hi, Back to the vexed old question, 'How to connect'
Eddy Vluggen wrote:
Which credentials did the Management Studio use to connect to the database? :rolleyes:
Well, I was running VS 2012 R2 and DB Management Studio in my Windows User Account(As per Standard Windows 7 Installation). No further questions were asked by the system. It appears that I can there create new db tables, and even populate them, but, cannot read or otherwise use them. Was this security system per chance written by 'Franz Kafka'?
Eddy Vluggen wrote:
Yes, but that's local to your machine - the database-server has it's own security. There's two modes; first there's Windows Authentication, which takes the user that's currently logged into Windows to authenticate (and is DBO when creating a table). One doesn't need a password in that case, since you already provided a username/password when logging into Windows. That's the preferred way. Alternative, there's "mixed mode", where SQL accepts a username/password combination (with "sa" usually being the name of the admin account).
In this case, the Databaseserver IS the local machine. I have since reset the database to 'Mixed Mode', and changed the password vor the 'sa' account to '12345'. I havechanged the connection string to:
SqlConnection myConnection = new SqlConnection("server=b-pc\\Softguard;"+
"database=SgTextiles; " +
"User ID=sa;Password=12345;" +
"Trusted_Connection=true;" +
"connection timeout=5");It now fails with:
Quote:
System.Data.SqlClient.SqlException (0x80131904): Cannot open database "SgTextiles" requested by the login. The login failed. Login failed for user 'b-PC\Bram'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean
My first priority now is to connect to the DB (by whatever means, including compromising security,) to get a proof of concept together :)
Bram van Kampen
-
Hi, Back to the vexed old question, 'How to connect'
Eddy Vluggen wrote:
Which credentials did the Management Studio use to connect to the database? :rolleyes:
Well, I was running VS 2012 R2 and DB Management Studio in my Windows User Account(As per Standard Windows 7 Installation). No further questions were asked by the system. It appears that I can there create new db tables, and even populate them, but, cannot read or otherwise use them. Was this security system per chance written by 'Franz Kafka'?
Eddy Vluggen wrote:
Yes, but that's local to your machine - the database-server has it's own security. There's two modes; first there's Windows Authentication, which takes the user that's currently logged into Windows to authenticate (and is DBO when creating a table). One doesn't need a password in that case, since you already provided a username/password when logging into Windows. That's the preferred way. Alternative, there's "mixed mode", where SQL accepts a username/password combination (with "sa" usually being the name of the admin account).
In this case, the Databaseserver IS the local machine. I have since reset the database to 'Mixed Mode', and changed the password vor the 'sa' account to '12345'. I havechanged the connection string to:
SqlConnection myConnection = new SqlConnection("server=b-pc\\Softguard;"+
"database=SgTextiles; " +
"User ID=sa;Password=12345;" +
"Trusted_Connection=true;" +
"connection timeout=5");It now fails with:
Quote:
System.Data.SqlClient.SqlException (0x80131904): Cannot open database "SgTextiles" requested by the login. The login failed. Login failed for user 'b-PC\Bram'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean
My first priority now is to connect to the DB (by whatever means, including compromising security,) to get a proof of concept together :)
Bram van Kampen
Bram van Kampen wrote:
Was this security system per chance written by 'Franz Kafka'?
Microsoft. If you can create, you can read. How do you create tables? Using SQL?
Bram van Kampen wrote:
I have since reset the database to 'Mixed Mode'
Remove the "Trusted_Connection=true" part from the connectionstring, and you should be able to connect.
Bram van Kampen wrote:
My first priority now is to connect to the DB
Should work if you change the connectionstring. I'll reply to the other post later today.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Hi, Eddy, A lot more food for thought!
Eddy Vluggen wrote:
How about a bit of both worlds? A single dedicated database-server, some cheap machine that's totally under your control, gifted to the client, running Linux. Have a MySQL (or SQLite) database on there, password-protect it, password protect the root, and do all communication over TCP/IP. Log every connection, every request.
I do not know which jurisdiction you live in. Jurisdiction has always an impact on software of the kind I write. It always has to take into consideration the legal system of the country in which we license it for use. Whereas we have Licence control over the software, we want to steer clear of providing hardware as far as possible, and even less of claiming ownership of hardware on customers sites. Softguard could easily become liable for loss of business and consequential losses if their 'owned' a crucial hardware component (in the case you suggest, the server) Fails in any way or another. This has to do with legal issues under the 'Sales of Goods Act' in the UK, an equivalent acts acros the EU. We provide Cash Drawers and Till Roll Printers for sale. These are easily guaranteed, relatively simple, and replaced under guarantee at nominal costs when failing. Our customers process financial data on the system. Our fiduciary duty of care includes protecting all data to the extent that it can only be released with the consent of the owner. If the owner dies, for whatever reason, and does not leave any access details to recover the information, then so be it; The data must die with the owner, whatever the consequences. Your example of a Nuclear Power Station is entirely wrong in the context. Our software is subject to the data protection act. If our software fails, it will never cause a nuclear explosion. 'MySql' I found very hard to come to terms with from scratch. The support is rather thin. I have over the last 6 years made at least three attempts to get it to go on Win XP. Learning to write in using SQL first, may be the better way for getting round to learning my way around. I take it that the difference between SQL and MySql statements can be largely handeled mechanically. (That was the purpose of 'Standard Queery Language' in the 1990's. At the same time, your suggestion makes sense! I can always provide a machine with a modified Linux/MySql version which excludes the windows logon to the database, even when copied to another regular mach
Bram van Kampen wrote:
Softguard could easily become liable for loss of business and consequential losses if their 'owned' a crucial hardware component (in the case you suggest, the server) Fails in any way or another. This has to do with legal issues under the 'Sales of Goods Act' in the UK, an equivalent acts acros the EU. We provide Cash Drawers and Till Roll Printers for sale. These are easily guaranteed, relatively simple, and replaced under guarantee at nominal costs when failing.
A database-server could be as simple as MySQL on a raspberry Pi (albeit no match for an x86). Give them two Pi's (or equivalents), each with a unique IP. Have the client-application write to both db-servers, and "read" from the one that's available :) ..or "sell" those to the client as part of the solution, so that it's no longer "your" apparatus. If you're not allowed to sell hardware, sell them two virtual machines with Linux. Might indeed be wise to have someone check the license before embarking on such a design.
Bram van Kampen wrote:
Your example of a Nuclear Power Station is entirely wrong in the context.
I'd rather be on the safe side and not find out.
Bram van Kampen wrote:
That was the purpose of 'Standard Queery Language' in the 1990's.
Still is. I try and keep stuff in SQL-92, old standard but seems to be well understood by most database-drivers.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Bram van Kampen wrote:
Was this security system per chance written by 'Franz Kafka'?
Microsoft. If you can create, you can read. How do you create tables? Using SQL?
Bram van Kampen wrote:
I have since reset the database to 'Mixed Mode'
Remove the "Trusted_Connection=true" part from the connectionstring, and you should be able to connect.
Bram van Kampen wrote:
My first priority now is to connect to the DB
Should work if you change the connectionstring. I'll reply to the other post later today.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Hi Eddy I am getting frustrated. Not with you, but, with this system. I very much appreciate your interest and time, but I am also getting embarased. Nothing seems to work! I returned to User Mode, this being the recommended mode for use by Microsoft. (in the current XP version, Windows Security is totally ignored altogether. We never had any security issues either) This version 3 will need to connect to the internet, so the security issue becomes important.
Eddy Vluggen wrote:
Should work if you change the connectionstring. I'll reply to the other post later today.
I had to shut down the terminal several times to get rid of the internal compiler error. When I eventually got it running, I got:
Quote:
System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'sa'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
About my reference to Franz Kafka. He is a (long dead, I think died 1924) european author. I also think, important reading for IT consultants, designers et all. Two of the books he wrote, 'The Process' and 'The Castle' are worth reading. Both deal each in a different way with an innocent and unsuspecting individual, getting involved in a life changing and enormous burocracy. These books were required reading for me in 1973, when I grauated secondary school. Good Idea to read them! More apt than ever! :)
Bram van Kampen
-
Hi Eddy I am getting frustrated. Not with you, but, with this system. I very much appreciate your interest and time, but I am also getting embarased. Nothing seems to work! I returned to User Mode, this being the recommended mode for use by Microsoft. (in the current XP version, Windows Security is totally ignored altogether. We never had any security issues either) This version 3 will need to connect to the internet, so the security issue becomes important.
Eddy Vluggen wrote:
Should work if you change the connectionstring. I'll reply to the other post later today.
I had to shut down the terminal several times to get rid of the internal compiler error. When I eventually got it running, I got:
Quote:
System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'sa'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
About my reference to Franz Kafka. He is a (long dead, I think died 1924) european author. I also think, important reading for IT consultants, designers et all. Two of the books he wrote, 'The Process' and 'The Castle' are worth reading. Both deal each in a different way with an innocent and unsuspecting individual, getting involved in a life changing and enormous burocracy. These books were required reading for me in 1973, when I grauated secondary school. Good Idea to read them! More apt than ever! :)
Bram van Kampen
Bram van Kampen wrote:
This version 3 will need to connect to the internet, so the security issue becomes important.
Do yourself a favor; download Sql Server Express and connect a machine you can miss to the internet. Dub it "honeypot". Or name it after a nuclear reactor :)
Bram van Kampen wrote:
I had to shut down the terminal several times to get rid of the internal compiler error.
When I eventually got it running, I got:Forget about passwords and using the Sql mixed mode business. With that "integrated security", things should work under your current user account, binding all things to the current Windows User under the alias of "dbo". Later, determine all access-points for the internet-user, write sprocs to fetch that data, and give the user from the IIS-process only execute-rights on those sprocs.
Bram van Kampen wrote:
I also think, important reading for IT consultants, designers et all. Two of the books he wrote, 'The Process' and 'The Castle' are worth reading.
Thanks, added them to my reading-list, got a long holiday in the late summer :) QPQ, the best book that I read this year was Small Gods[^].
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Bram van Kampen wrote:
Softguard could easily become liable for loss of business and consequential losses if their 'owned' a crucial hardware component (in the case you suggest, the server) Fails in any way or another. This has to do with legal issues under the 'Sales of Goods Act' in the UK, an equivalent acts acros the EU. We provide Cash Drawers and Till Roll Printers for sale. These are easily guaranteed, relatively simple, and replaced under guarantee at nominal costs when failing.
A database-server could be as simple as MySQL on a raspberry Pi (albeit no match for an x86). Give them two Pi's (or equivalents), each with a unique IP. Have the client-application write to both db-servers, and "read" from the one that's available :) ..or "sell" those to the client as part of the solution, so that it's no longer "your" apparatus. If you're not allowed to sell hardware, sell them two virtual machines with Linux. Might indeed be wise to have someone check the license before embarking on such a design.
Bram van Kampen wrote:
Your example of a Nuclear Power Station is entirely wrong in the context.
I'd rather be on the safe side and not find out.
Bram van Kampen wrote:
That was the purpose of 'Standard Queery Language' in the 1990's.
Still is. I try and keep stuff in SQL-92, old standard but seems to be well understood by most database-drivers.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Hi, Well, all good food for thought. There are now two issues. The first is an apparent bug in VS2012 which replicates a known but unresolved bug in VS2010. An internal error is caused, aparently by the Build Software being unable to create a pipe to the IDE. This stops the build from starting. (according to smart people on stackoverflow). It crept in end May, as an occasional annoyance, and has grown now to a major issue. However, there is not much I can do about it, MS has to resolve this. The second is that I need a proof of concept, which has no security implications. If I can make my SQL DB open to all callers (i.e. No Security checks whatsoever) I can show my supporters something that works. The security aspect is a matter of DB configuration and Connect String. That can be sorted independently at a later date. :)
Bram van Kampen
-
Hi, Well, all good food for thought. There are now two issues. The first is an apparent bug in VS2012 which replicates a known but unresolved bug in VS2010. An internal error is caused, aparently by the Build Software being unable to create a pipe to the IDE. This stops the build from starting. (according to smart people on stackoverflow). It crept in end May, as an occasional annoyance, and has grown now to a major issue. However, there is not much I can do about it, MS has to resolve this. The second is that I need a proof of concept, which has no security implications. If I can make my SQL DB open to all callers (i.e. No Security checks whatsoever) I can show my supporters something that works. The security aspect is a matter of DB configuration and Connect String. That can be sorted independently at a later date. :)
Bram van Kampen
Bram van Kampen wrote:
The second is that I need a proof of concept, which has no security implications. If I can make my SQL DB open to all callers (i.e. No Security checks whatsoever) I can show my supporters something that works.
How about switching temporarily to MS Access?
Bram van Kampen wrote:
The security aspect is a matter of DB configuration and Connect String. That can be sorted independently at a later date.
Nothing is as permanent as temporary code. If it works without "fixing", then there's a good chance it won't be fixed.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Bram van Kampen wrote:
The second is that I need a proof of concept, which has no security implications. If I can make my SQL DB open to all callers (i.e. No Security checks whatsoever) I can show my supporters something that works.
How about switching temporarily to MS Access?
Bram van Kampen wrote:
The security aspect is a matter of DB configuration and Connect String. That can be sorted independently at a later date.
Nothing is as permanent as temporary code. If it works without "fixing", then there's a good chance it won't be fixed.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Hi,
Eddy Vluggen wrote:
Nothing is as permanent as temporary code. If it works without "fixing", then there's a good chance it won't be fixed.
I appreciate that. However, we are dealing with 'Prototype' development here
Eddy Vluggen wrote:
How about switching temporarily to MS Access?
Well, Good Idea if it can be made to work! How do I access that with SQL, and do the SQL Constructs make sense for a pure SQL database. Regards:)
Bram van Kampen
-
Hi,
Eddy Vluggen wrote:
Nothing is as permanent as temporary code. If it works without "fixing", then there's a good chance it won't be fixed.
I appreciate that. However, we are dealing with 'Prototype' development here
Eddy Vluggen wrote:
How about switching temporarily to MS Access?
Well, Good Idea if it can be made to work! How do I access that with SQL, and do the SQL Constructs make sense for a pure SQL database. Regards:)
Bram van Kampen
Bram van Kampen wrote:
How do I access that with SQL,
You create a connection and execute commands. You'd need to change your connectionstring, and probably use the
OleDbConnectionBram van Kampen wrote:
and do the SQL Constructs make sense for a pure SQL database.
It uses SQL, but I don't know how the word "pure" fits in.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Bram van Kampen wrote:
How do I access that with SQL,
You create a connection and execute commands. You'd need to change your connectionstring, and probably use the
OleDbConnectionBram van Kampen wrote:
and do the SQL Constructs make sense for a pure SQL database.
It uses SQL, but I don't know how the word "pure" fits in.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Eddy Vluggen wrote:
Bram van Kampen wrote:
and do the SQL Constructs make sense for a pure SQL database.
It uses SQL, but I don't know how the word "pure" fits in.
Well, 'Pure' fits in as follows:- SQL stands(as you know) for 'Standard Queery Language', and was as such originally designed and intended to formulate queeries to a Database in a uniform way, for mutualy incompatible Databases in say DBase, Access, etc. I.E. it was an abstraction layer to create a common access method to Databases in various proprietary binary formats . MS SQL implements this abstraction layer, but can also be used to act as DB propper. That is: one can create a DB complete with tables and colums from scratch in SQL, without also specifying seperately an underlying Database (This is a step further than intended in the 1980's). This later concept is what I describe, (for want of a better word) as a'pure' SQL database. 'Pure' in the sense that to the user only SQL is involved, and no other underlying database. I understand from your answer that the connection string would be different. I extrapolate from the above that, provided Tables and Colums are identical in type and name, that the transaction and queery strings should not need changing when stepping back from Access to 'Pure' SQL. :)
Bram van Kampen
-
Eddy Vluggen wrote:
Bram van Kampen wrote:
and do the SQL Constructs make sense for a pure SQL database.
It uses SQL, but I don't know how the word "pure" fits in.
Well, 'Pure' fits in as follows:- SQL stands(as you know) for 'Standard Queery Language', and was as such originally designed and intended to formulate queeries to a Database in a uniform way, for mutualy incompatible Databases in say DBase, Access, etc. I.E. it was an abstraction layer to create a common access method to Databases in various proprietary binary formats . MS SQL implements this abstraction layer, but can also be used to act as DB propper. That is: one can create a DB complete with tables and colums from scratch in SQL, without also specifying seperately an underlying Database (This is a step further than intended in the 1980's). This later concept is what I describe, (for want of a better word) as a'pure' SQL database. 'Pure' in the sense that to the user only SQL is involved, and no other underlying database. I understand from your answer that the connection string would be different. I extrapolate from the above that, provided Tables and Colums are identical in type and name, that the transaction and queery strings should not need changing when stepping back from Access to 'Pure' SQL. :)
Bram van Kampen
Bram van Kampen wrote:
I extrapolate from the above that
Wrong assumption. Although you can force both to use SQL92, it's not optimal. Both have their own SQL-dialect, and there's a little difference in adding parameters to the IDbCommand. Still, I like Access for prototyping Sql-server database; one can build a UI quickly around the tables, and they're easily copied to a USB-stick. There's a Wizard somewhere in there that "upgrades" the database to Sql Server-format, so once it works you can migrate all the tables easy from Access. The wizard can't translate all, e.g., there's no UI-service in Sql Server. It also doesn't translate reports, but tables and views, yes. If you want to be able to use the Access-reports with the Sql Server database, that's possible too - by linking the tables from Access and installing the free Access Runtime[^].
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]