February 8, 2017 at 8:50 pm
Hi everyone, I imagine quite a few folks groan when they hear: SQL Connection String. I haven't had to deal with Connection Strings in a few years and this is my first with 2016SE and also first time working on WS2012R2
Its nice to know some things never seem to change much at MicroSlop. Given the plethora of sites dedicated to CxStrings and 30+years I'm amazed they still can't be straightforward if not simple.
Anyway, I hope someone here can help directly or refer me to some resource (even if its to a new career :w00t:)
Pretty generic install and setup. Standalone machine, single server, no virtuals
When I start managmentstudio:
Server Type: Database Engine
Server Name: MyServer
Authentication: Windows
I'm logged in as Administrator. I tried the string I've been using for years that didn't work so I searched.
I tried the first three strings (substituting of course) in this link:
https://www.connectionstrings.com/sql-server-2016/
no good on all of them
In the browser (IE11) the page settles with no content and no errors. The source looks like:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=windows-1252" http-equiv=Content-Type></HEAD>
<BODY></BODY></HTML>
I know the string is attempting to load because if I mess with the parms (eg a garbage server name) I do generate errors.
in SSMS, when I right-click on MyDatabase. The authentication shows MyServer\Administrator.
Under Product Server Name is MyServer, Instance Name is blank
Under Computer the Server Name is also MyServer
I'm not even sure what to ask. Is there some sort of checklist any one can suggest? See anything glaringly stupid in any of the above? Feel Free to suggest changes that would help make it (the db) more secure. It runs (ahem will run) on an intranet. This is a very simple setup (well, that's the intention). nothing fancy/obtuse/obscure at all.Certainly the desired scenario is very simple.
Thanks.
February 9, 2017 at 12:47 am
Can you post the connection string (obsfucate or remove server name, user name and password of course)?
I usually use https://www.connectionstrings.com/ if I need to look up options that I don't use often, haven't seen them wrong yet.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2017 at 8:14 am
I use that site as well. But the basics of a connections are as they are listed there.
Server = "Data Source=server name" in the connection string.
Database = "Initial Catalog=database name"
Username = "User Id=username"
Password = "Password=password"
unless you use integrated security and in an Intranet I don't believe that you will be doing integrated security unless you have the Web server application running as a specific user in the App Pool, but the integrated security piece is
Integrated Security = "Integrated Security=SSPI" or "Trusted Connection=true"
Otherwise, there are other things in a connection string that may enhance functionality with Availability Groups, etc. but the above things are basic items.
As Gail said, show us what you use for your connection string (obfuscated of course) so we can see if there is anything that is going wrong.
February 9, 2017 at 10:02 am
You mentioned you are using Windows authentication. This means your web server is attempting to connect using the account details for the web server service account. Has that account got the right privileges / accesses?
Thomas Rushton
blog: https://thelonedba.wordpress.com
February 9, 2017 at 10:12 am
mountcrumpit - Wednesday, February 8, 2017 8:50 PMHi everyone, I imagine quite a few folks groan when they hear: SQL Connection String. I haven't had to deal with Connection Strings in a few years and this is my first with 2016SE and also first time working on WS2012R2
Its nice to know some things never seem to change much at MicroSlop. Given the plethora of sites dedicated to CxStrings and 30+years I'm amazed they still can't be straightforward if not simple.
Anyway, I hope someone here can help directly or refer me to some resource (even if its to a new career :w00t:)
Pretty generic install and setup. Standalone machine, single server, no virtuals
When I start managmentstudio:
Server Type: Database Engine
Server Name: MyServer
Authentication: Windows
I'm logged in as Administrator. I tried the string I've been using for years that didn't work so I searched.
I tried the first three strings (substituting of course) in this link:
https://www.connectionstrings.com/sql-server-2016/
no good on all of them
In the browser (IE11) the page settles with no content and no errors. The source looks like:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=windows-1252" http-equiv=Content-Type></HEAD>
<BODY></BODY></HTML>
I know the string is attempting to load because if I mess with the parms (eg a garbage server name) I do generate errors.in SSMS, when I right-click on MyDatabase. The authentication shows MyServer\Administrator.
Under Product Server Name is MyServer, Instance Name is blank
Under Computer the Server Name is also MyServerI'm not even sure what to ask. Is there some sort of checklist any one can suggest? See anything glaringly stupid in any of the above? Feel Free to suggest changes that would help make it (the db) more secure. It runs (ahem will run) on an intranet. This is a very simple setup (well, that's the intention). nothing fancy/obtuse/obscure at all.Certainly the desired scenario is very simple.
Thanks.
local admins are no longer granted sql logi by default since sql server 2008, so this likely wont help.
Please provide more information where you are connecting from and to
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 9, 2017 at 8:02 pm
Hi all, thank you for your replies I appreciate the ideas and suggestions greatly. I'll do my best to provide all the info I've got (and be clear to say "i'm not sure" as needed
Initially it seemed I was having no trouble at all, when for a quick "what happens", I loaded the main page and it popped right up -- turns out I was finding the SQL2000 on a WS2003 machine on the network via the connection string which I'd neglected to update. I haven't been able to log into another installation of WS2003 (behind a VPN - need an updated PW) running SQL2005 to give that a Cstring a shot - though from your comments it seems like 2008 is a quasi-dividing line between primitive and somewhat up-to-date.
If it matters: (as ya'll have probably suspected) this is a somewhat vintage ClassicASP w/o .Net app. The client will start parallel on a "modern" app in ~May - but are paranoid about their 8 year old server until then. Enter a new machine running WS2012 with the vintage asp. Mine is not to judge. Ok so detach the network cable from the new WS2012 and start work:
Here are the majority of my attempts so far. They all return a blank page (see OP for html) except for the two marked Aha!
Sub Application_OnStart
'NG "Data Source=192.168.1.175,1433;Network Library=DBMSSOCN;Initial Catalog=MAIS;User ID=webMAIS;Password=userMAIS;"
'NG "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=webMAIS;password=userMAIS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Data Source=l-server;Initial Catalog=MAIS;Trusted_Connection=no;Workstation ID=mais"
'NG "Provider=SQLOLEDB.1;data source=L-SERVER;User ID=webMAIS;password=userMAIS;trustedconnection=no;Persist Security Info=False;Initial Catalog=MAIS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MAIS"
'NG "Server=L-Server;Database=MAIS;Trusted_ConnectionTrue;"
'NG "Server=L-Server;Database=MAIS;Integrated Security=True;"
'NG "Server=L-Server;Database=MAIS;Trusted Connection=true;Integrated Security=True;"
'NG "Server=L-Server\L-Server;Databse=MAIS;User ID=webMAIS;Password=userMAIS;"
' NG "Server=L-Server;Database=MAIS;Trusted Connection=true;Integrated Security=SSPI;"
'NG "Server=L-Server;Database=MAIS;Integrated Security=SSPI;"
'AHA! "Server=L-Server;Database=MAIS;User ID=webMAIS;password=userMAIS;Integrated Security=True;"
above gens: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
I kinda get what it doesn't like: integrated sec and a user ??
'AHA! Server=L-Server;Database=MAIS;User ID=webMAIS;password=userMAIS;
gens [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
ODBC? This seems to want an ole parm of some sort
' works for SQL2000 but !2016 Application("MAISSERVER_ConnectionString") = "Provider=SQLOLEDB.1;Server=Porter;Database=MAIS;User ID=webMAIS;password=userMAIS;trusted connection=no;"
Given that I'm by no means a guru maven etc. I approach this as an attempt to mimic the old setup - understanding that things have certainly changed since the last century (2000!hah)
I'll try to provide as much of the old connection/IIS info as I can:
L-Server\Administrators is the effective owner of MAIS in IIS
L-server\IUSR_L-Server has partial(?) deny on special permissions and Write
L-Server\Users has allow modify, r&E, r, w, list folder
Directory security is enabled anonymous
SQL2000
use Windows Authentication.
1) First Connectionstrings, Yes I tried https://www.connectionstrings.com/sql-server-2016/ when I began having trouble The first three - they seem deceptively simple and, well, they/I are/am missing something from them, they didn't work
I poked around more on that site and saw that some pre-2016 examples had the attribute: Provider=xxxxx and that xxxxx did not match SQLOLEDB of the 2000 ver.
2) in SQ2016 user webMAIS has the dbo as default schema
3) Since I'm sql connecting with user=webMAIS -- and webMAIS a user of MAIS what effect does the L-server\Administrator have on this?
I'm sorry this is kind of jumbled and lacking in direction. I know I'm ignorant but this seems particularly bewildering. My attempts feel like a depth-first search gone awry
If you' think screen shots would help more what would you like to see? I'm going to try and recover that page in connectionstrings.com that had the 2014 Provider attrib and try a few of those
Thank you!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply