Bulk Sms Server

[Browning Winters]

1What's the difference between the connection I created in Connection Manager on my server for type = "Microsoft Sql Server" that leverage "ODBC Driver 13 for Sql Server" vs a Sql Server Bulk connection?

2) How do I set up the DSN for a Sql Server Bulk connection? I need to do this so that the user will pass the name of the DSN and their credentials in the workflow to control user authentication (ex odbc: DSN=XXXX; UID=XXXX; PWD=XXXX

So now I see.....thanks for pointing that out. I was thinking there was some special driver needed that I didn't have. Once i switched to the native driver for the setup, it worked perfectly- took a 14 min process down to 14 seconds....

The SQL Server bulk loader is meant to improve the performance of data loads but it did not work with the default ODBC 13 Driver. So I downloaded the ODBC 11 driver from this link -us/download/details.aspx?id=36434

I have begrudgingly been updating our purchased wildcard certs manually for years and I've had enough now that I've got over 100 web and client servers. The servers are mostly independent and spread across a few different domains, but there are a couple farms running which might make this easier for at least those few.

My current process at the end of every certificate validity period is to purchase a new (renew) cert and complete the CSR from my in-house management box, then export the cert in .pfx format, and install it manually on each server in the Personal store. On web servers (IIS) I modify the bindings manually as well.

I know if I were using certs generated in the domain I could simply push them out using AD CA in each domain, but based on my research I cannot find a way to roll-out the cert we purchased from the vendor.

I also see that a GPO might at least be the answer here for getting the cert on the servers - it won't be hard for me to setup item-level targeting, or put all the web-servers in a group or OU in each domain. The only issue I am having with this is I cannot find a way to use this method to place the cert in the Personal store, which is a requirement.

There is likely to be more than 1 "right" answer here, but I'd like to know how you guys tackle this process every year so feel free to chime in. Apologies if this has been answered in this community before, but my search did not bear fruit.

Sorry for the long post, but this is a robust and thorough solution to my problem. I had myself a couple extra drinks after work for besting this one. Feel free to send me a drink if this works for you!

This issue came down to Kerberos Delegation. In one case they were wanting to use Full Trust delegation, but there was some confusion on the CIFS principal. We don't need to add a CIFS Principal . You should just be able to enable Delegation for the SQL Service Account and it should work.

On the Constrained Delegation side of things, it turned out that we had to also enable Constrained Delegation on the Machine account of SQL Server as well as the SQL Service Account. This was due to how SMB2 works and will not always have the context of the user and instead be in the context of the System Account.

The File is sitting on a different server than what SQL Server is running on. Therein lies the problem. If we look at Process Monitor , which is a free SysInternals tool, we can see the Access Denied. This was run on the SQL Server, because that is where the CreateFile API call is made.

The issue here is really about delegation. The SQL Server needs to be trusted to delegate my credential to another server/service. We see issues like this crop up because, typically, SQL Server is the back end server and the last stop on the journey of a connection/credential. So, in most cases, SQL Server will not be trusted for delegation. It is usually the Web Server or Application Server that is trusted for delegation because they want to get to SQL Server.

I have two options here. The first option is "Trust this user for delegation to any service (Kerberos Only)" which I refer to as "Full Trust". The other option, "Trust this user for delegation to specified services only", is Constrained Delegation and is more secure because you are explicitly allowing delegation for certain services and not a blanket pass.

I've had mixed results with restarting the SQL Service vs restarting the whole box (see this blog post ), you may get away with just restarting the service, but you may need to reboot the box. After I restarted, I see the following:

That took care of one issue I was looking at. But, I was presented with another one that indicated they wanted to do Constrained Delegation. Initially their setup was not correct. When we go to use Constrained Delegation, we have to be specific about what service we specify.

Because we are hitting a file server, we are interested in the CIFS service. One thing I've seen people do is go to create the CIFS SPN because when they go to look at the Machine Account for the file server, they don't see it.

However, CIFS is covered by the HOST entries. Similar to HTTP. We do not need to add a CIFS SPN. However, with the Constrained Delegation, we do need to add the CIFS Service there. It should show up because the HOST entry is present on the Machine Account.

We know Full Trust worked! So, why didn't Constrained Delegation work? I had enabled Kerberos Event logging earlier to catch items. So, when I look at the System Event Log on the SQL Server, I see the following:

It is showing the Machine name, not the Service Account or my User account. That is not what I would have expected. Because we are seeing the Machine Account in this respect, that would explain why it failed, because I haven't setup any Delegation settings for the Machine Account. Only the SQL Service Account. Let's see what happens when I set the Delegation settings on the Machine Account.

The real issue here is due to the use of SMB2 and the redirector that I used. Due to the code path that we end up coming down for Constrained Delegation within LSASS, we do not have the context of the user. Instead, we have the context of the System Account. This is why we saw captthrace$ in the Kerb Event Log entry when it wasn't expected.

SMB2 is more asynchronous to maximize performance and causes you to run into this issue with Constrained Delegation. You could actually hit this with SMB1 as well, but it isn't likely as most requests will come from a thread that has the context of the user.

Note: If you are running a SQL Cluster, and want to configure Constrained Delegation, you have to do it on each physical node machine account in the cluster. Doing this on only the virtual node account will not allow this to work properly.

I've created a set of SSIS packages which run fine in our test environment. The packages access a SQL server database on the local server. I can change the data source to use Windows or SQL Server authentication and both work.

We've transferred the packages to another server, and again are running them against a local sql server database. Using Windows authentication they work fine, but when I change them to use SQL Server authentication they fail with :-

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 5(Access is denied.). Make sure you are accessing a local server via Windows security.".

From the documentation I understand that the SQL Server Data Flow Destination needs permission to create Global Objects - but what needs that permission assigned? Or any other ideas how to cure the problem?

The issue I've picked up is that if you accidentally use a SQL Server Destination in your Data Flow Tasks and you have to change your Destination to an OLE DB one, you have to sit and re-map your columns from scratch - Which is an extremely huge pain if you have hundreds of columns to map.

In my mind this sort of defeats the use of the SQL Server Destination because it now would commit rows to your Database every time it hits the X amount of rows you have specified, but at least you wouldn't have to go and re-map all of your columns from scratch.

Users who execute packages that include the SQL Server destination require the "Create global objects" permission. You can grant this permission to users by using the Local Security Policy tool opened from the Administrative Tools menu. If you receive an error message when executing a package that uses the SQL Server destination, make sure that the account running the package has the "Create global objects" permission.

Yes Great Guys.. I was facing same probelm and the below solution have solve my probekl but i did not understand why it is happening, if i have a facility to choose the SQL Server Destination thay why i will go for OLE DB provider.

Has anyone resolved this problem OTHER than switching away from the SQL Server Destination? I've seen lots of posts about this and the "solution" seems to be to use OLE DB Destination which I understand is slower.

When i am bulk editing prices and other information on multiple products, I get a server error and the circle on the right just keep spinning and never saves. When i refresh the page, it shows some product information updated but not all. I notice i get the error when i have 5 or more products selected to edit

If none of the above works, could you please send me a screenshot of what the server error looks like, and let me know what specific field you're editing when this occurs, or if this just happens regardless of what fields are edited.

Lana Social Care @ Shopify

-Was my reply helpful? Click Like to let me know!

- Was your question answered? Mark it as an Accepted Solution

- To learn more visit the Shopify Help Center or the Shopify Blog

I went though all that you mentioned above and am still getting the error. Attached is a screen shot of what it does. It stays on that screen and the little circle thing in the upper right corner just spins. A red box pops up on the bottom that says Server Error but goes away after a few seconds. It happends regardless of what fields im editing. Ive tried editing a different amount of products to see if there is a certain number that causes it, but having had much luck.

3a8082e126